|
View previous topic :: View next topic
|
| Author |
Message |
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
Is there a way to dynamically build unload and load statements based on the tables in SYSIBMTABLES?
I have over 400 tables and want to know if there is a better way to do this.
Any ides/suggestions would be greatly appreciated.
Thanks! |
|
| Back to top |
|
 |
Pedro
Global Moderator
Joined: 01 Sep 2006
Posts: 2608
Location: Silicon Valley
|
|
|
|
I think there is a way; I think that is how IBM's DB2 Admin Tool builds the unload jobs for you. Though, I think you use SYSIBM.SYSCOLUMNS (or something like that).
During the job, the UNLOAD utility generates the matching LOAD statement to the PUNCHDDN file (I think) |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3087
Location: NYC,USA
|
|
|
|
| You can write a cobol program that does build the card for you and submit that JCL dynamically using inititiator. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1387
Location: Bamberg, Germany
|
|
|
|
| Sounds intransparent at least. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
| Rohit Umarjikar wrote: |
| You can write a cobol program that does build the card for you and submit that JCL dynamically using inititiator. |
COBOL is not a convenient choice for this task, though it may be used too, by COBOLfils.
SYNCSORT utility provides the interface to get the required data directly from DB2 tables (e.g. from SYSIBM.SYSTABLES, et.al.)
When using the extracted table names, the following SORT statements can easily build any desired statements as text output: either LOAD/UNLOAD, or whatever else. Moreover, two different sets of control statements can be built at once: one to unload the required tables, plus one to load the same tables later.
The next step of the same JCL, after this SORT step, can call the required utility, and substitute the output from SORT step as input control statements for this utility (LOAD/UNLOAD, or anything else).
The whole JCL would include only two JCL steps in this case. |
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
Rohit and/or Sergeyken,
Would you have a simple example to accomplish this?
I started down the path of manually doing this as specific tables I wanted to unload data based on a SELECT statement, e.g., Select * from Table where CYCLE_DATE > '2024-12-31'.
Since most tables have the different column names for cycle dates, e.g., CYCLE_END_DATE, CYC_END_DTE, etc., I manually started to code for each one.
any assistance would be greatly appreciated. |
|
| Back to top |
|
|
Pedro
Global Moderator
Joined: 01 Sep 2006
Posts: 2608
Location: Silicon Valley
|
|
|
|
hmmm, I am confused by the terminology being used.
From the topic sentence, I thought you were talking about the DB2 utilities for UNLOAD and LOAD, but the examples described are for DB2 SELECT statements.
I was expecting the stand alone UNLOAD utility with statements like:
| Code: |
UNLOAD TABLESPACE DSN8D12A.DSN8S81E NOPAD
FROM TABLE DSN8C10.EMP
(EMPNO, LASTNAME, SALARY DECIMAL EXTERNAL) |
I do not believe that using SELECT is considered an UNLOAD.
And, as mentioned earlier, you can use SELECT from SYSIBM.SYSCOLUMNS to get the columns that belong to a particular table in order to build the UNLOAD statement. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
| chillmo wrote: |
Rohit and/or Sergeyken,
Would you have a simple example to accomplish this?
I started down the path of manually doing this as specific tables I wanted to unload data based on a SELECT statement, e.g., Select * from Table where CYCLE_DATE > '2024-12-31'.
Since most tables have the different column names for cycle dates, e.g., CYCLE_END_DATE, CYC_END_DTE, etc., I manually started to code for each one.
any assistance would be greatly appreciated. |
Your initial intention is not what you demonstrate now...
Nevertheless, the approach of using SYNCSORT is the same; the rest are "minor technical details".
The general template is as follows.
I always avoid to give a ready-to-copy-and-paste solution.
| Code: |
//SYSTABS EXEC PGM=SYNCSORT,PARM='DB2=dbsys'
// . . . . . . . . .
//UNLDCNTL DD DISP=(NEW,PASS),...,
// DSN=&&UNLOAD
//LOADCNTL DD DISP=(NEW,PASS),...,
// DSN=&&LOAD
//SYSIN DD *
SELECT NAME TABLE_NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR="creator"
AND NAME LIKE "prefix%"
SORT FIELDS=COPY
OUTFIL FNAMES=UNLDCNTL,
REMOVECC,
BUILD=(C' UNLOAD ......',
/,C' FROM creator.',
a,b, - positions of table name from SELECT; find by yourself!
/,C'additional parameters for UNLOAD')
OUTFIL FNAMES=LOADCNTL,
REMOVECC,
BUILD=(C' LOAD ......',
/,C' INTO creator.',
a,b, - positions of table name from SELECT; find by yourself!
/,C'additional parameters for LOAD')
END
//*
// . . . . . . . . |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
It would be nice if you presented here THE EXACT SAMPLE: what you really need to produce from SYSIBM.SYSTABLES?
The "theoretical speech" is often not clear, or doubtful. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
Sorry, I do not have access to mainframe right now, that's why I missed some statements in my examples, taken from memory.
It should be used like this:
| Code: |
//SYSTABS EXEC PGM=SYNCSORT,PARM='DB2=dbsys'
// . . . . . . . . .
//UNLDCNTL DD DISP=(NEW,PASS),...,
// DSN=&&UNLOAD
//LOADCNTL DD DISP=(NEW,PASS),...,
// DSN=&&LOAD
//*
//SORTDBIN DD *
SELECT NAME TABLE_NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR="creator"
AND NAME LIKE "prefix%"
//*
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FNAMES=UNLDCNTL,
REMOVECC,
BUILD=(C' UNLOAD ......',
/,C' FROM creator.',
a,b, - positions of table name from SELECT; find by yourself!
/,C'additional parameters for UNLOAD')
OUTFIL FNAMES=LOADCNTL,
REMOVECC,
BUILD=(C' LOAD ......',
/,C' INTO creator.',
a,b, - positions of table name from SELECT; find by yourself!
/,C'additional parameters for LOAD')
END
//*
// . . . . . . . . |
|
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
Here's what I'm trying to create dynamically, as I mentioned, I'm currently doing this manually for 400 tables.
| Code: |
//**********************************************************************
//* DELETE EXISTING DATA
//**********************************************************************
//TABLE1 EXEC PGM=IEFBR14
//SYSREC00 DD DSN=&PREF..TABLE1.DATA&SYS,&DSBR14
//SYSPUNCH DD DUMMY
//*
//**********************************************************************
//* UNLOAD DATA - SYSREC00 CONTAINS THE DATA
//**********************************************************************
//TABLE1 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN='DB2.LINKLIB',DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=&PREF..TABLE1.DATA&SYS,&DSBR15
//SYSPUNCH DD DSN=&PREF..CNTLCRD&SYS(TABLE1),DISP=OLD
//SYSIN DD *
CREATOR.TABLE1
//SYSTSIN DD *
DSN SYSTEM(DB2SYS)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) LIB('DB2.LOAD')
//*
|
As an aside, I wanted to know if I can for specific tables, if I can add a SELECT statement to reduce the number of rows I unload.
| Code: |
//**********************************************************************
//* DELETE EXISTING DATA
//**********************************************************************
//TABLE2 EXEC PGM=IEFBR14
//SYSREC00 DD DSN=&PREF..TABLE2.DATA&SYS,&DSBR14
//SYSPUNCH DD DUMMY
//*
//**********************************************************************
//* UNLOAD DATA - SYSREC00 CONTAINS THE DATA
//**********************************************************************
//TABLE2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN='DB2.LINKLIB',DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=&PREF..TABLE2.DATA&SYS,&DSBR15
//SYSPUNCH DD DSN=&PREF..CNTLCRD&SYS(TABLE2),DISP=OLD
//SYSIN DD *
SELECT *
FROM CREATOR.TABLE2
WHERE EXP_DTE > '2024-12-31';
//SYSTSIN DD *
DSN SYSTEM(DB2SYS)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL') -
LIB('DB2.LOAD')
//*
|
|
|
| Back to top |
|
|
Pedro
Global Moderator
Joined: 01 Sep 2006
Posts: 2608
Location: Silicon Valley
|
|
|
|
from the DSNTIAUL description:
| Quote: |
| Each input statement must be a valid SQL SELECT statement with the clause SELECT * FROM omitted and with no ending semicolon. |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
So far, there are two completely different, and independent issues:
1) how to code the correct JCL and correct control statements for DSNTIAUL utility, in order to extract (not to unload!) selected data from a particular DB2 table?
2) how to automate the process of creation of the code mentioned above for the whole (or a partial) list of tables listed in the DB2 system catalog table(s)?
Remains absolutely unclear: how this planned AI system would automatically decide: which fields to be extracted from every particular DB2 table?!?! |
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
Sergeyken,
Sorry for the confusion.
The original request was asking is there a way to automate the creation of the code, above, for tables listed in SYSIBM.SYSTABLES? Since I have 400 tables, I didn't want to do this manually.
Then, as an aside, I wanted to know in addition to above, there are a few tables, where the business only wanted specific dates, and wanted to know if this could be automated as well.
Thanks! |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
| chillmo wrote: |
Sergeyken,
Sorry for the confusion.
The original request was asking is there a way to automate the creation of the code, above, for tables listed in SYSIBM.SYSTABLES? Since I have 400 tables, I didn't want to do this manually.
Then, as an aside, I wanted to know in addition to above, there are a few tables, where the business only wanted specific dates, and wanted to know if this could be automated as well.
Thanks! |
Use of SYSTABLES to generate any control code is described in general in my (corrected) template above. |
|
| Back to top |
|
|
|
|
|
