Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2012
    Posts
    177

    Unanswered: Issues in the db2 tables

    Hi all,

    In our setup db2v9.7 with fixpack 4

    Facing issues in the db2 tables, Creating the table with DATE (data type column)

    After creation when I checked the DDL it's looks as TIMESTAMP(data type)

    Checked the DB parameters,

    Number compatibility = on
    Varchar2 compatibility = on
    Date compatibility = on

    How to set these parameter's to OFF?

    [db2inst1@vrdtisdbhcl01 ~]$ db2 update db cfg using number_compat = OFF
    SQL0104N An unexpected token "number_compat" was found following "USING".
    Expected tokens may include: "ALT_COLLATE". SQLSTATE=42601


    thanks,
    Babu.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Babu,

    The date_compat database parameter can't be changed after the database creation.
    If you whant to change such behavior - drop the database and recreated it.

    P.S.:
    You have to omit '=' sign in the 'update db cfg':
    Code:
    db2 update db cfg using number_compat OFF
    Regards,
    Mark.

  3. #3
    Join Date
    Sep 2012
    Posts
    177
    Hi Mark,

    In our setup we have 4 partitioned db's under the instance.

    Rest of the three db's the parameter's are in off state only.

    Number compatibility = off
    Varchar2 compatibility = off
    Date compatibility = off

    Issue with in the particular table only, When we create the table using the DATE(data type), but after creation DATE column is TIMESTAMP.

    for this db the parameters are like this:

    Number compatibility = ON
    Varchar2 compatibility = ON
    Date compatibility = ON

    How to update this parameter to OFF?

    Checked with the DB2_COMPATIBILITY_VECTOR but the value is null only.

    Thanks,
    Babu.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The compatibility vector setting in most part takes effect during the database creation, as Mark pointed out. You will need to extract your existing data, drop and re-create the database making sure DB2_COMPATIBILITY_VECTOR is unset, then load your data back.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2012
    Posts
    177
    Hi ,

    Thanks for your reply.

    In our setu having one instance it contains four partitioned db's.

    Rest of the db's are fine, but at the initial creation time not mentioned any parameters.

    Problem occurs for this particular db only.

    Can I do db2set DB2_COMPATIBILITY_VECTOR =

    Start and stop the instance. Changes will reflect for this db?

    thanks,
    Babu.

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by laxman babu View Post
    Can I do db2set DB2_COMPATIBILITY_VECTOR =

    Start and stop the instance. Changes will reflect for this db?
    Babu,

    no, you can't do so to change this parameter.
    Once again, the only way to change it is to recreate the database.
    Regards,
    Mark.

  7. #7
    Join Date
    Sep 2012
    Posts
    177
    Mark,

    Thanks for your reply.

    While the creation of database how to mention this parameter.

    If I took offline backup of the db, and tried to restore to the new db. How it will change?

    Could you please let me know.

    Thanks,
    Babu.

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by laxman babu View Post
    While the creation of database how to mention this parameter.
    DB2 looks at the bit position 7 (0x40) of the DB2_COMPATIBILITY_VECTOR registry variable upon a database creation. If this bit is set then the date_compat parameter will be set to ON.
    Quote Originally Posted by laxman babu View Post
    If I took offline backup of the db, and tried to restore to the new db. How it will change?
    It will not change this parameter.

    Babu, there is no "easy way" to switch this parameter to OFF.
    Regards,
    Mark.

  9. #9
    Join Date
    Sep 2012
    Posts
    177
    Mark,

    Thanks for your reply.

    Initially you mentioned, Way to switch off this parameter by the database creation time.

    How to create the database using this parameter?

    Thanks,
    Babu.

  10. #10
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by laxman babu View Post
    Initially you mentioned, Way to switch off this parameter by the database creation time.

    How to create the database using this parameter?
    If you have DB2_COMPATIBILITY_VECTOR registry variable set to some value, then you have to set it to empty value and restart the instance:
    Code:
    db2set DB2_COMPATIBILITY_VECTOR=
    db2stop
    db2start
    After that you can create your database.

    Otherwise you can create your database immediately.
    Regards,
    Mark.

  11. #11
    Join Date
    Sep 2012
    Posts
    177
    Mark,

    Thanks for your reply.

    Even I tried with
    db2set DB2_COMPATIBILITY_VECTOR=
    db2stop
    db2start

    and restart the instance, but the db parameter not changed.

    you are telling need to create the new database, restore the backup image to the new db right?

    Thanks,
    Babu.

  12. #12
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Babu,

    Please, read the following statement carefully:
    YOU ARE NOT ABLE TO CHANGE THIS PARAMETER OF EXISTING DATABASE.
    No way. Even restoring the database from a backup.
    I've already said the same multiple times.
    Regards,
    Mark.

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    This turns out to be one of the funniest threads on this forum.
    ---
    "It does not work" is not a valid problem statement.

  14. #14
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    so True. I am surprised you didn't direct him to page 172 of the manual though.

  15. #15
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I stand corrected. Here is the funniest thread on the forum.
    HTML Code:
    http://www.dbforums.com/microsoft-sql-server/1695356-show-users-whether-btheyve-done-traing-not.html

Posting Permissions

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