DB2: Extract definition of a global temp table created using “as select”

[dbadmin2]

In a perl dbi script, I create a db2 temp table on database A using "as select" to define columns. Then, I need to redefine the temp table on another database B. Does any of you have any hints on how to generate the ddl of the global temp table on database A?

 

[fredericofonseca]

what operating system and what version of DB2?

and what is your full statement please - 2 types of temp tables can be created, and one of them is available on SYSIBM.SYSTABLES and the other isn't

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[dbadmin2]

Frederico,
Version is Db2 9.7. Operating system=linux.
The type of temp table is GTT (global temporary table).
Inside the DBI Pert script, the GTT is created in database A using "create temporary table xyz as select a.column1, b.column2, ...".
Then, in the same DBI Perl script, I need to generate the ddl of the GTT from DB A, in order to define it on DB B.
Do you have any hints how to generate the ddl of the GTT inside the perl dbi script?

Mike

 

[fredericofonseca]

To be clear the type of table is a "created global temporary table" (the other type is a "declared global temporary table")

2 possible ways. Not sure if there are any more.

1 - if table description is available on sysibm.systables (which it should be in this case) you can query this table directly and generate your create table DDL.

2 - use of describe command (see

https://www-01.ibm.com/support/know...doc/r0002019.html?cp=SSEPGG_9.7.0/3-6-2-4-27)

with this you have 2 options
2.1 - describe the new table itself
2.2 - describe the output of the sql that created it.
For this you would execute the sql again, but with one option on the where clause that would make it not to process any rows e.g. "where 0 = 1 "

either of the methods above will return you enough information for you to parse and build a create table ddl.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886