Create table like & Insert into

db2maf · #1 (**permalink**) 09-20-10, 18:53

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.

db2girl · #2 (**permalink**) 09-20-10, 19:42

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.

db2maf · #3 (**permalink**) 09-20-10, 21:24

Thanks Bella and yes there must be an easier way to achieve this.

Mark.

ARWinner · #4 (**permalink**) 09-21-10, 08:28

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

db2girl · #5 (**permalink**) 09-21-10, 09:03

Quote:

Originally Posted by ARWinner

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

I like this one.

tonkuma · #6 (**permalink**) 09-21-10, 10:06

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.

Quote:

My system: DB2 v9.5_FP0 (Windows).

So, I don't know you can use that option or not.