Derive DB2 Dataset Names from the Catalog

Topic: Code Place

Subtopic: DB2 for z/OS

Multi-statement SQL-only script, should be executable in all standard DB2 SQL processors (Data Studio, TEP2, SPUFI, Command Processor).

Create Declared Global Temporary Tables; populate with DB2 Catalog Entry elements needed to create names of the underlying VSAM datasets; UPDATE to populate with derived (hopefully real1) dataset names (The DSNDBD name). Works for partitioned, clones, indexes to same. Not sure about LOBs.  This has been tested in our subsystems and appears to work correctly.  Rigorous comparison has not been done.

A sample query is included as the last element. Please feel free to modify to your own needs -- if you come up with something that's derivatve and creates increased value (VSAM DEFINE, anyone?), please post it here.

No warranties, folks; check your results!

UPDATE 2016-03-03: Roy Boxwell has created an edited version of the original, which omits DEFINE NO spaces, creates a HLQ of "DB2 CAT" for spaces in DSNDB01 and DSNDB06, and adds spaces in to the SQL for certain function invocations as required by European SQL syntax. Updated version uploaded herewith. (s) Phil Sevetson

Click Here to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.