IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to use DSNTIAUL in jcl to fetch data from table.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ramankapoor

New User


Joined: 27 Feb 2006
Posts: 23
Location: delhi

PostPosted: Tue Mar 14, 2006 5:30 pm
Reply with quote

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
View user's profile Send private message
ofer71

Global Moderator


Joined: 27 Dec 2005
Posts: 2358
Location: Israel

PostPosted: Tue Mar 14, 2006 5:38 pm
Reply with quote

The DSNTIAUL example program is well documented (with example) in the fine manual.

O.
Back to top
View user's profile Send private message
martin9

Active User


Joined: 01 Mar 2006
Posts: 290
Location: Basel, Switzerland

PostPosted: Tue Mar 14, 2006 6:52 pm
Reply with quote

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
View user's profile Send private message
ramankapoor

New User


Joined: 27 Feb 2006
Posts: 23
Location: delhi

PostPosted: Thu Mar 16, 2006 10:26 am
Reply with quote

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
View user's profile Send private message
T-REXDB2

New User


Joined: 28 Apr 2005
Posts: 11
Location: USA

PostPosted: Sun Mar 19, 2006 8:38 am
Reply with quote

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
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Mon May 21, 2007 6:11 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue May 22, 2007 5:43 am
Reply with quote

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
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Tue May 22, 2007 2:53 pm
Reply with quote

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
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Tue May 22, 2007 6:24 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue May 22, 2007 7:03 pm
Reply with quote

Good to hear that it is working.

Thank you for letting us know icon_smile.gif
Back to top
View user's profile Send private message
Senthil KK

New User


Joined: 03 Aug 2012
Posts: 10
Location: India

PostPosted: Wed Oct 24, 2012 2:05 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Wed Oct 24, 2012 2:30 pm
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India

PostPosted: Wed Oct 24, 2012 6:26 pm
Reply with quote

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
View user's profile Send private message
Senthil KK

New User


Joined: 03 Aug 2012
Posts: 10
Location: India

PostPosted: Wed Oct 24, 2012 8:04 pm
Reply with quote

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 icon_sad.gif and no hex chars are there.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Oct 24, 2012 8:22 pm
Reply with quote

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
View user's profile Send private message
Senthil KK

New User


Joined: 03 Aug 2012
Posts: 10
Location: India

PostPosted: Wed Oct 24, 2012 8:36 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Oct 24, 2012 8:40 pm
Reply with quote

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
View user's profile Send private message
Senthil KK

New User


Joined: 03 Aug 2012
Posts: 10
Location: India

PostPosted: Wed Oct 24, 2012 9:29 pm
Reply with quote

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 icon_smile.gif

Thanks for your time.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Wed Oct 24, 2012 9:47 pm
Reply with quote

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 icon_smile.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts db2 vs static data COBOL Programming 1
No new posts External data queue (changes?) CLIST & REXX 0
No new posts Pull data using date difference betwe... DB2 6
No new posts fast data scrambling PL/I & Assembler 10
No new posts Unload and Load ISPF Table TSO/ISPF 4
Search our Forums:

Back to Top