Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Db2 udb dba

  1. #1
    Join Date
    Aug 2012
    Posts
    23

    Unanswered: Db2 udb dba

    Hi
    i am new to DB2 DBA.i need urgent help.i am using DB2 v9.7 on RHEL 5.5.
    while i am creating the sequences in DB2 command mode it is giving the error like
    invalid specification of an identity coloumn or sequence object reason code 2.
    the syntax is
    create sequence "ADMIN"."BOB_VALUE" minvalue 1 maxvalue 99999999999999999999999999999 increment by 1 start with 1000 no cache no order no cycle.
    it is executing up to 9 digits.not more than 9"
    SQLCODE:0846N SQLSTATE:42815
    please do the needful any help will greatly appreciated......
    thanks.............

  2. #2
    Join Date
    Apr 2012
    Posts
    156
    My understanding is the default data type for a sequence is an integer. The max value for an integer is +2,147,483,647

    I would redefine the sequence as a BIGINT. Take a look at the following documentation if you need more info:

    Db2 limits:
    DB2 Database for Linux, UNIX, and Windows

    Create Sequence Syntax:
    DB2 Database for Linux, UNIX, and Windows

  3. #3
    Join Date
    Aug 2012
    Posts
    23
    As u said The max value for an integer is +2,147,483,647 but the problem is same sequence creation in another instance is executed successfully.so i want to resolve this issue.kindly post your valuable reply............

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by raveendra View Post
    create sequence "ADMIN"."BOB_VALUE" minvalue 1 maxvalue 99999999999999999999999999999 increment by 1 start with 1000 no cache no order no cycle.
    ...
    The max value for an integer is +2,147,483,647 but the problem is same sequence creation in another instance is executed successfully
    Are you sure that the maxvalue you're specifying in another instance is 99999999999999999999999999999 and data type INT?

  5. #5
    Join Date
    Aug 2012
    Posts
    23
    yes,i used the same value in another instance which is the data type int.for your reference pls check the above sql query...

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    From the working instance, please execute the following statements and provide the output:

    db2 create sequence test1 as int maxvalue 2147483649
    db2 create sequence test2 as bigint maxvalue 9223372036854775808


    Also, include db2level output.

  7. #7
    Join Date
    Aug 2012
    Posts
    23
    i didn't mention any datatype in the sql query.eventhough get executed.the query which i executed is as follows.
    db2 "create sequence "ADMIN"."BOB_VALUE" minvalue 1 maxvalue 99999999999999999999999999999 increment by 1 start with 1000 no cache no order no cycle";

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Please provide db2level output from that instance.

  9. #9
    Join Date
    Aug 2012
    Posts
    23
    db2 version 9.7 fix pack 0

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    $ db2level
    DB21085I Instance "db2inst2" uses "64" bits and DB2 code release "SQL09070"
    with level identifier "08010107".
    Informational tokens are "DB2 v9.7.0.0", "s090521", "AIX6497", and Fix Pack
    "0".
    Product is installed at "/opt/IBM/db2/V9.7".


    $ db2 "create sequence "ADMIN"."BOB_VALUE" minvalue 1 maxvalue 99999999999999999999999999999 increment by 1 start with 1000 no cache no order no cycle"
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0846N Invalid specification of an identity column or sequence object
    "SEQUENCE" "ADMIN.BOB_VALUE". Reason code = "2". SQLSTATE=42815


    As you can see, maxvalue 99999999999999999999999999999 is not valid. The max you can specify using int is:

    $ db2 create sequence test maxvalue 2147483647
    DB20000I The SQL command completed successfully.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by raveendra View Post
    i didn't mention any datatype in the sql query.eventhough get executed.the query which i executed is as follows.
    db2 "create sequence "ADMIN"."BOB_VALUE" minvalue 1 maxvalue 99999999999999999999999999999 increment by 1 start with 1000 no cache no order no cycle";
    It may have something to do with the Oracle compatibility features.

    What does this show: "db2 get db cfg for <yourdb> | grep COMPAT"?
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I tried with DB2_COMPATIBILITY_VECTOR=ORA, the same results:

    $ db2 get db cfg for oracle | grep -i compat
    Number compatibility = ON
    Varchar2 compatibility = ON
    Date compatibility = ON


    $ db2 "create sequence "ADMIN"."BOB_VALUE" minvalue 1 maxvalue 99999999999999999999999999999 increment by 1 start with 1000 no cache no order no cycle"
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0846N Invalid specification of an identity column or sequence object
    "SEQUENCE" "ADMIN.BOB_VALUE". Reason code = "2". SQLSTATE=42815


    raveendra, from your working instance, create another sequence with the same maxvalue and provide the output. In addition, provide:

    db2set -all
    db2 get dbm cfg
    db2 get db cfg for <db name>

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Why not just leave off the min and max specifications and it will default to the max for the data type?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Aug 2012
    Posts
    23
    As you said my working instance from the db2level output is as follows.
    DB21085I Instance "db2inst2" uses "64" bits and DB2 code release "SQL09070"
    with level identifier "08010107".
    Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXAMD6497", and Fix
    Pack "0".
    Product is installed at "/opt/ibm/db2/V9.7".

    the sequence command is executed from the working instance is db2inst1 and instance2 is db2inst2 o/p for grep compat is as follows.
    [db2inst1@PSLLNX09 ~]$ db2 get db cfg for testdb | grep -i compat
    Number compatibility = ON
    Varchar2 compatibility = ON
    Date compatibility = ON

    [db2inst2@PSLLNX09 ~]$ db2 get db cfg for testdb | grep -i compat
    Number compatibility = OFF
    Varchar2 compatibility = OFF
    Date compatibility = OFF
    and also as you said i had executed the command from instance db2inst1 and db2inst2 is

    [db2inst2@PSLLNX09 ~]$ db2 create sequence ipshdb2.test1 as int maxvalue 2147483649
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0846N Invalid specification of an identity column or sequence object
    "SEQUENCE" "IPSHDB2.TEST1". Reason code = "2". SQLSTATE=42815

    [db2inst2@PSLLNX09 ~]$ db2 create sequence ipshdb2.test2 as bigint maxvalue 9223372036854775808
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0846N Invalid specification of an identity column or sequence object
    "SEQUENCE" "IPSHDB2.TEST2". Reason code = "2". SQLSTATE=42815
    and also i have tried for compatability change which is showing like

    [db2inst2@PSLLNX09 ~]$ db2 "update db cfg for testdb using ALT_COLLATE IDENTITY_16BIT"
    SQL5113N ALT_COLLATE cannot be updated for a Unicode database.
    25826

    and finally the db2set command for inst1 and inst2 is

    [db2inst1@PSLLNX09 ~]$ db2set -all
    [i] DB2_COMPATIBILITY_VECTOR=ORA
    [i] DB2_ENABLE_LDAP=no
    [i] DB2COMM=TCPIP
    [g] DB2SYSTEM=PSLLNX09
    [g] DB2INSTDEF=db2inst1
    [g] DB2ADMINSERVER=dasusr2

    [db2inst2@PSLLNX09 ~]$ db2set -all
    [i] DB2_COMPATIBILITY_VECTOR=ORA
    [i] DB2COMM=TCPIP
    [g] DB2SYSTEM=PSLLNX09
    [g] DB2INSTDEF=db2inst1
    [g] DB2ADMINSERVER=dasusr2
    so please give the reply as soon as possible any help will greatly appreciated

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Looks like in an Oracle-compatible database the default datatype for a sequence is DECIMAL(27) for some reason, while in a normal database it defaults to INTEGER.

    Code:
    [db2inst9@oc2522778176 ~]$ db2 "create sequence TwentySevenDigits maxvalue 999999999999999999999999999"
    DB20000I  The SQL command completed successfully.
    [db2inst9@oc2522778176 ~]$ db2 "create sequence TwentyEightDigits maxvalue 9999999999999999999999999999"
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0846N  Invalid specification of an identity column or sequence object 
    "SEQUENCE" "DB2INST9.TWENTYEIGHTDIGITS". Reason code = "2".  SQLSTATE=42815
    [db2inst9@oc2522778176 ~]$ db2 "create sequence TwentyEightDigits as decimal(28) maxvalue 9999999999999999999999999999"
    DB20000I  The SQL command completed successfully.
    [db2inst9@oc2522778176 ~]$
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •