Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2010
    Location
    Australia
    Posts
    7

    Unanswered: Create table like & Insert into

    Hi Folks,

    New to this site.
    My system: DB2 v9.5_FP0 (Windows).

    I have two questions that I seek help with:

    1. I want to create a table using the following:
    - db2 create table SCHEMA.DBCFG like SYSIBMADM.DBCFG
    however, I want to add a 'snapshot_timestamp' column. Is it possible to achieve that with the create command above or do I have to perform an 'alter table' post the 'create like' command.

    2. Having created the table in step 1. above I want to perform inserts using the following command with the inclusion of 'snapshot_timestamp':
    insert into SCHEMA.DBCFG select * from sysibmadm.dbcfg
    (I'm not sure how to insert the timestamp with this command).

    Look forward to your responses,
    Thanking you in advance.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    I was able to insert using:

    db2 "create table dbcfg like SYSIBMADM.DBCFG"
    db2 "alter table dbcfg add column timestamp timestamp default"
    db2 "insert into dbcfg (NAME,VALUE,VALUE_FLAGS,DEFERRED_VALUE,DEFERRED_VA LUE_FLAGS,DATATYPE,DBPARTITIONNUM) select * from SYSIBMADM.DBCFG"


    But there must be an easier way to do this.

  3. #3
    Join Date
    Sep 2010
    Location
    Australia
    Posts
    7
    Thanks Bella and yes there must be an easier way to achieve this.

    Mark.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You will have to do the create/alter table steps, I do not see a way around that. But the insert statement can be like this:

    Code:
    insert into dbcfg select d.*,current timestamp from sysibmadm.dbcfg as d

    Andy

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Quote Originally Posted by ARWinner View Post
    insert into dbcfg select d.*,current timestamp from sysibmadm.dbcfg as d
    I like this one.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try:
    Code:
    CREATE TABLE db2admin.dbcfg 
    AS (
    SELECT dc.*
         , CURRENT_TIMESTAMP AS snapshot_timestamp
     FROM  sysibmadm.dbcfg dc
    )
    WITH NO DATA
    ;
    
    INSERT INTO db2admin.dbcfg
    SELECT dc.*
         , CURRENT_TIMESTAMP  AS snapshot_timestamp
     FROM  sysibmadm.dbcfg dc
    ;

    Rationale follows...

    1) According to the manual "DB2 Version 9.5 for Linux, UNIX, and Windows SQL Reference, Volume 2 Updated March, 2008",
    "as-result-table" option was supported by DB2 9.5.

    Syntax:

    CREATE TABLE table-name as-result-table ...

    as-result-table:
    [(column-name [, column-name ...])] AS ( fullselect ) WITH NO DATA

    2) But, you are using FP0.
    My system: DB2 v9.5_FP0 (Windows).
    So, I don't know you can use that option or not.

  7. #7
    Join Date
    Sep 2010
    Location
    Australia
    Posts
    7
    Dear all,

    Thank you for your ideas. You have resolved my issues and given me options to work with.

    Much appreciated.
    Mark.

Posting Permissions

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