INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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?

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

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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

(OP)
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


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

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/knowledgecenter/SSE...)

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close