View previous topic :: View next topic
Author
Message
sasanka Warnings : 1 New User Joined: 18 Jan 2008Posts: 34 Location: India
Hi,
I have a table (TABLE1) whose description is given below
Code:
CUS_ID Integer Not Null
,ROW_ID RowId Not Null
,CUS_DO BLOB(26214400) Not Null
,ROW_CREATE_TS Timestamp Not Null
,ROW_CREATE_PGM_NM Char(8) Not Null
,ROW_LAST_TS Timestamp Not Null
,ROW_LAST_PGM_NM Char(8) Not Null
I need to write a JCL to unload data from the above table to flat file. So how do I write an unload JCL for TABLE1.
Thanks in advance.
Back to top
sushanth bobby Senior Member Joined: 29 Jul 2008Posts: 1020 Location: India
Sasanka,
DB2 V9 Utility Reference Guide --> Unload --> Example 11
Thanks,
Sushanth
Back to top
sasanka Warnings : 1 New User Joined: 18 Jan 2008Posts: 34 Location: India
Hi, I have executed the following jcl, but it gives MAXCC = 8.
Code:
//STEP05R EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DISP=SHR,DSN=DB2GR0C.RUNLIB.LOAD
// DD DISP=SHR,DSN=SYSAPF.DBMS.DB2GR0C.SDSNLOAD
//*
//SYSTSIN DD *
DSN SYSTEM(GR0C)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL)
/*
//*
//SYSREC00 DD DISP=(NEW,CATLG,DELETE),
// UNIT=CSDTSO,
// LABEL=RETPD=045,
// SPACE=(23400,(1500,300),RLSE),
// DSN=TEST.UNLD.V1PMRODC.DEVPMR.DX021011
//*
//SYSPUNCH DD DISP=(NEW,CATLG,DELETE),
// UNIT=CSDTSO,
// LABEL=RETPD=045,
// SPACE=(23440,(100,20),RLSE),
// DSN=SBSNEOG.SYSPUNCH.UNLOAD.ODC.DX021011
//*
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//*
//SYSIN DD *
TEMPLATE LOBFRV DSN 'TEST.ODMPFDO.UNLOAD.DX021011'
DSNTYPE(PDS) UNIT(SYSDA)
UNLOAD DATA
FROM TABLE TESTTAB.TABLE1
(
CUS_ID INTEGER NOT NULL
,ROW_ID ROWID NOT NULL
,CUS_DO VARCHAR(255) BLOB LOBFRV
,ROW_CREATE_TS TIMESTAMP NOT NULL
,ROW_CREATE_PGM_NM CHAR(8) NOT NULL
,ROW_LAST_TS TIMESTAMP NOT NULL
,ROW_LAST_PGM_NM CHAR(8) NOT NULL
)
SHRLEVEL CHANGE
/*
//*
Can anyone provides me the correct jcl to unload above mentioned table data.
Sasanka
Back to top
dick scherrer Moderator Emeritus Joined: 23 Nov 2006Posts: 19244 Location: Inside the Matrix
Hello,
Is there some reason you did not post the diagnostic information that was generated. . .
When posting JCL, and control statements, you need to use the Code tag.
Suggest you reply with the Coded info you already posted along with the diagnostic info for someone to use to help you.
Back to top
sushanth bobby Senior Member Joined: 29 Jul 2008Posts: 1020 Location: India
Sasanka,
First, DSNTIAUL is not for running DB2 Utilities. You need to use DSNUTILB.
Second, DSNTIAUL can unload LOB files, but only up to 32KB. For more infomation on DSNTIAUL you can check,
DB2 V9 Application Programing & SQL Guide --> Search for DSNTIAUL
Here is a sample to get you started,
Code:
//STEP01 EXEC PGM=DSNUTILB,COND=(4,LT),
// PARM='DB2D'
//STEPLIB DD DISP=SHR,DSN=DB2.DB2D.DSNEXIT
// DD DISP=SHR,DSN=DB2.DB2D.DSNLOAD
//SYSPRINT DD SYSOUT=*
//SYSREC DD DSN=HXSULL.NLOB.SMPLUNLD,DISP=(NEW,KEEP),
// UNIT=SYSDA,
// SPACE=(TRK,(5,5),RLSE)
//UTPRINT DD SYSOUT=*
TEMPLATE LOBFRV DSN &USERID..LOB.SMPLUNLD.&TS.
UNIT SYSDA DISP (NEW,CATLG,CATLG) SPACE (2,1) CYL
UNLOAD DATA
FROM TABLE DSN8810.EMP_PHOTO_RESUME
(EMPNO CHAR(6),
RESUME VARCHAR(255) CLOBF LOBFRV)
SHRLEVEL CHANGE
Thanks,
Sushanth
Back to top
Raghu navaikulam Active User Joined: 27 Sep 2008Posts: 193 Location: chennai
Hi Sasanka
Try this
Code:
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN8)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
LIB('DSN810.SDSNLOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=userid.DB2.DATA1X,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(MOD,CATLG)
//SYSREC01 DD DSN=userid.DB2.DATA2X,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(MOD,CATLG)
//SYSREC02 DD DSN=userid.DB2.DATA3X,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(MOD,CATLG)
//SYSPUNCH DD DSN=userid.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(MOD,CATLG),
// RECFM=FB,LRECL=80,BLKSIZE=6160
//SYSIN DD *
EMPLOYEE;
DEPT;
PROJECT;
/*
EMPLOYEE;
DEPT;
PROJECT; are table name.
There is one SYSRECxx for one table.
Hope this will help you
Regards
Raghu
Back to top
sasanka Warnings : 1 New User Joined: 18 Jan 2008Posts: 34 Location: India
Hi Sushanth,
I executed the below JCl and found error as described below
Code:
//STEP05R EXEC PGM=DSNUTILB,PARM='DB2D'
//STEPLIB DD DISP=SHR,DSN=DB2GR0C.RUNLIB.LOAD
// DD DISP=SHR,DSN=SYSAPF.DBMS.DB2GR0C.SDSNLOAD
//*
//SYSPRINT DD SYSOUT=*
//*
//SYSREC00 DD DISP=(NEW,CATLG,DELETE),
// UNIT=CSDTSO,
// LABEL=RETPD=045,
// SPACE=(23400,(1500,300),RLSE),
// DSN=TEST.UNLD.V1PMRDOC.DEVPMR.DX140211
//*
//SYSPUNCH DD DISP=(NEW,CATLG,DELETE),
// UNIT=CSDTSO,
// LABEL=RETPD=045,
// SPACE=(23440,(100,20),RLSE),
// DSN=SBSNEOG.SYSPUNCH.UNLOAD.ODC.DX021011
//*
//UTPRINT DD *
TEMPLATE LOBFRV DSN 'TEST.ODMPFDO.UNLOAD.DX021011'
UNIT(SYSDA) DISP(NEW,CATLG,DELETE) SPACE(2,1) CYL
DSNTYPE(PDS) UNIT(SYSDA)
UNLOAD DATA
FROM TABLE PMRWTBT.V1PMRODC
(
CUS_ID INTEGER NOT NULL
,ROW_ID ROWID NOT NULL
,CUS_DO VARCHAR(255) BLOB LOBFRV
,ROW_CREATE_TS TIMESTAMP NOT NULL
,ROW_CREATE_PGM_NM CHAR(8) NOT NULL
,ROW_LAST_TS TIMESTAMP NOT NULL
,ROW_LAST_PGM_NM CHAR(8) NOT NULL
)
SHRLEVEL CHANGE
/*
//*
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//*
After executing the above JCL, I got the following error diagnostic information
Code:
ABONL-31: DDSDINTF ATTACH LIST FAILED, RC = 000001EC, GROUP = ML101
ABONL-18: NO DATABASES AVAILABLE
IGD104I SYS5.ABENDAID.LSD RETAINED, DDNAME=SYS00004
AB401 ===== ABEND-AID =====
REPORT WRITTEN TO: ABENDAID.JOB-SBSNEOGU STEP-STEP05R .........
ABEND CODE=S04E
IEA995I SYMPTOM DUMP OUTPUT
SYSTEM COMPLETION CODE=04E REASON CODE=00E40002
TIME=06.55.43 SEQ=02184 CPU=0000 ASID=0039
PSW AT TIME OF ERROR 078D1000 800094C2 ILC 2 INTC 0D
ACTIVE LOAD MODULE ADDRESS=00007000 OFFSET=000024C2
NAME=DSNUTILB
DATA AT PSW 000094BC - 00181610 0A0DA7E5 02A850F0
GR 0: 84000000 1: 8404E000
2: 00000002 3: 0000007C
4: 00009EAC 5: 00006990
6: 00000000 7: 00000008
8: 00006AAC 9: 00009EDF
A: 00000004 B: 800093C0
C: 00006B28 D: 00006B28
E: 800094AA F: 00E40002
END OF SYMPTOM DUMP
IEF472I SBSNEOGU STEP05R - COMPLETION CODE - SYSTEM=04E USER=0000 REASON=00E4000
Please advice.
edited to add the code tags and a friendly suggestion
the code tags...use the effing code tags
Back to top
enrico-sorichetti Superior Member Joined: 14 Mar 2007Posts: 10873 Location: italy
Quote:
NO DATABASES AVAILABLE
Quote:
SYSTEM COMPLETION CODE=04E REASON CODE=00E40002
look Yourself at the manuals and find the meaning of
SYSTEM COMPLETION CODE=04E
and
REASON CODE=00E40002
the only people who will be able to help You are those from Your support group...
( understand what resource is not available and make it available to Your process )
this is a professional forum, and the expectation is that a professional
should be able to look at the manuals himself
and if it is not so,
he should migrate to the beginners forums where he will get plenty of pointers to the manuals
or meditate about a career change
Back to top
Please enable JavaScript!