|
View previous topic :: View next topic
|
| Author |
Message |
ramankapoor
New User
Joined: 27 Feb 2006
Posts: 23
Location: delhi
|
|
|
|
How to use DSNTIAUL in jcl to fetch data from table.
is there any default tables in DB2 on which i can do little practice as there are emp table ,dept table in oracle..
Pls suggest.. |
|
| Back to top |
|
 |
ofer71
Global Moderator
Joined: 27 Dec 2005
Posts: 2358
Location: Israel
|
|
|
|
The DSNTIAUL example program is well documented (with example) in the fine manual.
O. |
|
| Back to top |
|
|
martin9
Active User
Joined: 01 Mar 2006
Posts: 290
Location: Basel, Switzerland
|
|
|
|
hy ramakapoor,
here is an example:
//SQL EXEC PGM=IKJEFT01,
// DYNAMNBR=100
//STEPLIB DD DSN=SYS1.DSNXDP0.RUNLIB.LOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(XDP0)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS ('SQL')
END
//SYSPUNCH DD DSN=structure,DISP=(NEW,CATLG,),
// UNIT=SYSDA,
// SPACE=(TRK,(5,5),RLSE),
// RECFM=FB,LRECL=80
//SYSREC00 DD DSN=data,DISP=(NEW,CATLG,),
// SPACE=(CYL,(20,20,),RLSE),
// DCB=(LRECL=111,RECFM=FB)
//SYSIN DD *
SELECT GTGE.OBKLASSECODE,
GTGE.ORDNUNGSBEGRIFF,
GTDE.DOKUREFTYPCODE,
GTDE.ZUSATZDOKUMENTID,
GTDE.DOKUMENTARTCODE,
GTDE.ERSTELLTAM,
GTDE.GTDE_LAUFNUMMER
FROM Z00.TGTDE_DOKUEINTRAG GTDE,
Z00.TGTGE_GVGBEINTRAG GTGE
WHERE DOKUREFTYPCODE ='DISO'
AND GTDE.GTGE_LAUFNUMMER = GTGE.GTGE_LAUFNUMMER
AND GTGE.OBKLASSECODE = 7
AND GTGE.ERSTELLTAM < CURRENT TIMESTAMP - 3 YEAR
AND GTDE.DOKUMENTARTCODE = 'I71'
ORDER BY 2
;
i hope you can use it...
note: the select is an internal one, you have other tables.
for practising, choose an existing SYSIBM.SYS% table...
martin9 |
|
| Back to top |
|
|
ramankapoor
New User
Joined: 27 Feb 2006
Posts: 23
Location: delhi
|
|
|
|
Pls Tell me the meaning of all these statments :
// DYNAMNBR=100 (meaning)
//STEPLIB DD DSN=SYS1.DSNXDP0.RUNLIB.LOAD,DISP=SHR
(where this dsn resides and why we need this)
DSN SYSTEM(XDP0) (what is the meaning of XDP0)
PLAN(DSNTIAUL) (Why we are putting the same name DSNTIAUL for the plan) |
|
| Back to top |
|
|
T-REXDB2
New User
Joined: 28 Apr 2005
Posts: 11
Location: USA
|
|
|
|
| ramankapoor wrote: |
How to use DSNTIAUL in jcl to fetch data from table.
is there any default tables in DB2 on which i can do little practice as there are emp table ,dept table in oracle..
Pls suggest.. |
Dear Sir,
Attached, please find DSNTIAUL sample JCL stream (rows are "fetched" into output file DDNAME SYSREC00) and list of z/OS DB2 V7 Sample tables:
//STEP0A EXEC PGM=IKJEFT01 --- V7 SAMPLE DSNTIAUL
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB71) -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
DSN8710.PROJ WHERE DEPTNO='D88'
//*
Activity table (DSN8710.ACT)
Department table (DSN8710.DEPT)
Employee table (DSN8710.EMP)
Employee photo and resume table (DSN8710.EMP_PHOTO_RESUME)
Project table (DSN8710.PROJ)
Project activity table (DSN8710.PROJACT)
Employee to project activity table (DSN8710.EMPPROJACT)
HOPE THIS HELPS, MUCHLY!!! ;-] |
|
| Back to top |
|
|
girias
New User
Joined: 09 Feb 2007
Posts: 26
Location: Chennai
|
|
|
|
Hi,
I have a dataset which is having the keyvalues to be given in the where condition. when I tried submitting the job, I am getting a sql error code of -104.
But when I tried giving the values directly, instead of dataset its working fine....
My code looks as follows... Please advise...
| Code: |
//STEP120 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -
LIB('DSN710.RUNLIB.LOAD')
//SYSREC00 DD DSN=dataset.unload,
// DISP=(NEW,CATLG,DELETE),
// UNIT=TSTDA,SPACE=(CYL,(1,1))
//SYSPUNCH DD DSN=dataset.DB2.SYSPUNCH,
// UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,CATLG)
//*
//SYSREC01 DD DUMMY
//SYSIN DD *
SELECT * FROM table
WHERE col1 IN (
// DD DSN=dataset,DISP=SHR
// DD *
);
/*
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
|
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Please post the first and last few entries in the file that contains the values you are trying to use - use copy/paste rather than typing them.
Also please post all of the diagnostic info presented by the failed query - not just the -104. |
|
| Back to top |
|
|
girias
New User
Joined: 09 Feb 2007
Posts: 26
Location: Chennai
|
|
|
|
Hi Dick,
I have attached some sample values of the file and the complete error message. Please take a look.
| Code: |
1997,
543,
347,
2018,
0000 |
| Code: |
1 DSNT490I SAMPLE DATA UNLOAD PROGRAM
0 DSNT505I DSNTIAUL OPTIONS USED: SQL
0 DSNT503I UNLOAD DATA SET SYSPUNCH RECORD LENGTH SET TO 80
0 DSNT504I UNLOAD DATA SET SYSPUNCH BLOCK SIZE SET TO 27920
0
0 DSNT502I ERROR IN DSNTIAUL SQL INPUT:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "COL_1". SOME SYMBOLS THAT MIGT BE LEGAL ARE: + -
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 158 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF' X'0000009E' X'00000000' SQL DIAGNOSTIC
INFORMATION
|
Thanks in Advance for your quick response |
|
| Back to top |
|
|
girias
New User
Joined: 09 Feb 2007
Posts: 26
Location: Chennai
|
|
|
|
Hi,
I got this issue resolved. I gave the dataset rec-length as 80. The job is running fine now.
Thanks
Giri |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Good to hear that it is working.
Thank you for letting us know  |
|
| Back to top |
|
|
Senthil KK
New User
Joined: 03 Aug 2012
Posts: 10
Location: India
|
|
|
|
Hi
I am getting the same error as he got. I ve kept the dataset record length as 80 and still getting the error.
[/img]
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "*". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: . TABLE <END-OF-STATEMENT>
QUERYNO HAVING WHERE GROUP SKIP
Below the query i ve given as control card
BROWSE IN90909.DB2LIB(CH001Q05) -
Command ===>
********************************* Top of
SELECT * FROM PRD_CODES
********************************* |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
any reason to resurrect a 5 years old topic
and not start a new one ?
also why waste resources posting a 1 MB jpeg when a plain text cut and paste with the CODE tags would convey the same info using about 3KB
here is a sample of a 3270 screen TEXTED
| Code: |
Menu Utilities Compilers Options Status Help
──────────────────────────────────────────────────────────────────────────────
ISPF Primary Option Menu
Option ===>
0 Settings Terminal and user parameters User ID . : ******
1 View Display source data or listings Time. . . : 00:08
2 Edit Create or change source data Terminal. : 3278
3 Utilities Perform utility functions Screen. . : 2
4 Foreground Interactive language processing Language. : ENGLISH
5 Batch Submit job for language processing Appl ID . : ISR
6 Command Enter TSO or Workstation commands TSO logon : $$ISPF1
7 Dialog Test Perform dialog testing TSO prefix:
8 Dismissed - was LMF System ID : ****
9 Dismissed - was other IBM Products MVS acct. : ****
10 SCLM SW Configuration Library Manager Release . : **** z.z
11 Workplace ISPF Object/Action Workplace
12 z/OS System z/OS system programmer applications
13 z/OS User z/OS user applications
14 z/OS System z/OS System Applications (Alternate)
15 z/OS User z/OS user applications (Alternate)
16 z/OS Other z/OS Other (Condensed)
Enter X to Terminate using log/list defaults |
|
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
With what you've posted, the speculations are:
1. Try passing SYSIN arguments
| Code: |
| SELECT * FROM PRD_CODES |
directly in the JCL instead of a member from a PDS.
2. You've got this
| Quote: |
| DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "*". |
- check if you've correctly coded the '*' in your query.
3. Verify if the member IN90909.DB2LIB(CH001Q05) does not have any unnecessary character in it. HEX-ON and check. |
|
| Back to top |
|
|
Senthil KK
New User
Joined: 03 Aug 2012
Posts: 10
Location: India
|
|
|
|
Hi,
I didn't notice its a 5 yr old thread.
I have a requirement to use the program DSNTIAUL to unload data from table.
I will paste the contents moving forward instead of attaching files.
I tried giving the query in SYSIN but it still failed  and no hex chars are there. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
How many rows are in the table?
Does the query work if entered in SPUFI (only if there are not tons of rows)?
Does a SELECT COUNT(*) work?
What happens if you include the select inline instead of in a member?
Are there any other "unload" queries that successfully run? |
|
| Back to top |
|
|
Senthil KK
New User
Joined: 03 Aug 2012
Posts: 10
Location: India
|
|
|
|
Query runs fine outside the program. I mean using QMF.
There are hardly 2000 rows in the table.
Yes, other unload and load jobs runs fine. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| What happens if you include the select inline instead of in a member? |
Did you try this?
Are any of the successful unloads being run with the query in this particular dataset?
What is the dcb info for the library being used?
Are there any sequence numbers in this member? |
|
| Back to top |
|
|
Senthil KK
New User
Joined: 03 Aug 2012
Posts: 10
Location: India
|
|
|
|
Hi Dick,
Unloads jobs being run in my environment are quite different. I modified the jcl a bit to suit my environment and it works now
Thanks for your time. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Well, if there are additional site-specific conditions, they would surely need to be met . . .
Good to hear it is working - thank you for letting us know
d |
|
| Back to top |
|
|
|
|
