Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to run a query using INZUTILB
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
beto981

New User


Joined: 06 Oct 2006
Posts: 24

PostPosted: Fri Dec 18, 2009 7:05 pm    Post subject: How to run a query using INZUTILB
Reply with quote

I'm trying to run some querys in batch mode, using a sequential file as input, but an abend occurs. Here's the code:

Code:
//C6T0291  EXEC PGM=INZUTILB,PARM='DSN5',REGION=8M       
//STEPLIB  DD  DSN=DSN5.SDSNEXIT,DISP=SHR                 
//         DD  DSN=DSN5.SDSNLOAD,DISP=SHR                 
//         DD  DSN=SYS3.HPU.V21.SINZLINK,DISP=SHR         
//SYSTSPRT DD  SYSOUT=*                                   
//SYSREC00 DD  DSN=OUTPUT.FILE, 
//             DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,       
//             SPACE=(TRK,(1,500),RLSE),                 
//             DCB=(BLKSIZE=0,DSORG=PS,LRECL=500,RECFM=FB)
//SYSPUNCH DD  DUMMY                                     
//SYSPRINT DD  SYSOUT=*                                   
//SYSOUT   DD  SYSOUT=*                                   
//SYSUDUMP DD  SYSOUT=*                                   
//SYSIN    DD  DSN=INPUT.FILE,DISP=SHR


The input file has the following querys:

SELECT * FROM TABLE1
WHERE CLIENT IN (XXXX,YYYYY,ZZZZZ)
AND NAME IN (AAAA,BBBB,CCCC)
AND CODE = 1
WITH UR;

SELECT * FROM TABLE2
WHERE CLIENT IN (XXXX,YYYYY,ZZZZZ)
AND NAME IN (AAAA,BBBB,CCCC)
AND DATE = '18.12.2009'
WITH UR;

The error message:
INZU039E FATAL SYNTAX ERROR BEFORE LINE 114 COLUMN 73

In the line 114 I have just the "WITH UR;" of the last query.

Do I have to use a special parameter to run theese querys? Can anyone help me with this problem? Tks!
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Fri Dec 18, 2009 7:20 pm    Post subject:
Reply with quote

You can try with single query.
Back to top
View user's profile Send private message
beto981

New User


Joined: 06 Oct 2006
Posts: 24

PostPosted: Fri Dec 18, 2009 7:30 pm    Post subject:
Reply with quote

Yes, I know. But I have 10 files with different querys each one, and the idea is to use a single jcl and just change the desired input file. That's why I'm trying to use a file as input.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Fri Dec 18, 2009 7:40 pm    Post subject:
Reply with quote

Then you have to use multiple SYSREC's like this.

//SYSREC00 DD DSN=OUTPUT.FILE,
// DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,
// SPACE=(TRK,(1,500),RLSE),
// DCB=(BLKSIZE=0,DSORG=PS,LRECL=500,RECFM=FB)
//SYSREC01 DD DSN=OUTPUT.FILE1,
// DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,
// SPACE=(TRK,(1,500),RLSE),
// DCB=(BLKSIZE=0,DSORG=PS,LRECL=500,RECFM=FB)
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Fri Dec 18, 2009 8:40 pm    Post subject: Reply to: How to run a query using INZUTILB
Reply with quote

did You check the settings of the ISPF number mode

anything happening around column 73 makes me suspicious about extraneous characters left around
( and usually ISPF line numbers belong to that kind )
Back to top
View user's profile Send private message
beto981

New User


Joined: 06 Oct 2006
Posts: 24

PostPosted: Fri Dec 18, 2009 10:13 pm    Post subject: Re: Reply to: How to run a query using INZUTILB
Reply with quote

enrico-sorichetti wrote:
did You check the settings of the ISPF number mode

anything happening around column 73 makes me suspicious about extraneous characters left around
( and usually ISPF line numbers belong to that kind )



Enrico, there's line numbers only at the left side of the file. Like this:

Code:
EDIT       INPUT.FILE                                   Columns 00001 00072
Command ===>                                                  Scroll ===> CSR 
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
000114 WITH UR;         
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Dec 18, 2009 10:24 pm    Post subject:
Reply with quote

Quote:
Enrico, there's line numbers only at the left side of the file. Like this:


those are the edit/view session linenumbers; what Enrico was talking about was line numbers as part of the ds.

Quote:
Columns 00001 00072


which is why your display was useless, twice.
12.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Fri Dec 18, 2009 10:24 pm    Post subject: Reply to: How to run a query using INZUTILB
Reply with quote

Uhhh, what is in 73 and beyond. . .?

While the line numbers may be to the left, there may be other junk to the right. . .

d
Back to top
View user's profile Send private message
beto981

New User


Joined: 06 Oct 2006
Posts: 24

PostPosted: Fri Dec 18, 2009 10:48 pm    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
Quote:
Enrico, there's line numbers only at the left side of the file. Like this:


those are the edit/view session linenumbers; what Enrico was talking about was line numbers as part of the ds.

Quote:
Columns 00001 00072


which is why your display was useless, twice.
12.gif


Sorry... but here's the rest of the file.

Code:
EDIT       INPUT.FILE                                                Columns 00009 00080
Command ===>                                                  Scroll ===> CSR 
000114                                                                         
Back to top
View user's profile Send private message
beto981

New User


Joined: 06 Oct 2006
Posts: 24

PostPosted: Tue Dec 22, 2009 12:35 am    Post subject: Reply to: How to run a query using INZUTILB
Reply with quote

Searching for examples, I found this jcl for unload:

Code:
//SYSIN    DD  *
   UNLOAD TABLESPACE                                           
   DB2 NO   LOCK NO QUIESCE YES QUIESCECAT NO                   
   SELECT XXXX, YYYY, ZZZZZ
           FROM TABLE
           OUTDDN SYSREC00                   
           FORMAT DSNTIAUL


I changed the parameter DB2 to YES and executed the query that I cited previously, and it worked.

Now, does anyone knows if it's prossible and how to use theese parameters with an input file instead the query in the SYSIN, as in the beggining of the topic?

Tks!
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Tue Dec 22, 2009 1:12 am    Post subject:
Reply with quote

Hello,

Quote:
does anyone knows if it's prossible and how to use theese parameters with an input file instead the query in the SYSIN

You can place the statements from the most recent SYSIN into multiple datasets or members and concatenate them into the run. . .

This however does not sound like the original question. . .

I run multiple batch queries from multiple members in the same job/step rather regularly. . .
Back to top
View user's profile Send private message
beto981

New User


Joined: 06 Oct 2006
Posts: 24

PostPosted: Tue Dec 22, 2009 1:20 am    Post subject:
Reply with quote

You mean this?

//SYSIN DD DSN=INPUT.FILE,DISP=SHR



INPUT.FILE:

DB2 NO LOCK NO QUIESCE YES QUIESCECAT NO
SELECT * FROM TABLE1
WHERE CLIENT IN (XXXX,YYYYY,ZZZZZ)
AND NAME IN (AAAA,BBBB,CCCC)
AND CODE = 1
WITH UR;



Tks!
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Tue Dec 22, 2009 1:36 am    Post subject: Reply to: How to run a query using INZUTILB
Reply with quote

Yup,

d
Back to top
View user's profile Send private message
beto981

New User


Joined: 06 Oct 2006
Posts: 24

PostPosted: Tue Dec 22, 2009 2:04 am    Post subject:
Reply with quote

Isn't worked D....

INZU010I ITEM STARTING AT (36,1) ENDING AT(36,7)
INZU097E SYNTAX ERROR - UNRECOGNIZED ELEMENT SELECT FOR SELECT BLOCK.

The file:

000001 UNLOAD TABLESPACE
000002 DB2 YES LOCK NO QUIESCE YES QUIESCECAT NO
.......
000016 SELECT * FROM TABLE
000017 WHERE XXXX IN (YYYY,ZZZZZZ)
......
000032 WITH UR;
000033 --
000034 --
000035 --
000036 SELECT * FROM TABLE2
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Tue Dec 22, 2009 3:04 am    Post subject:
Reply with quote

Hello,

I believe i misunderstood. . . I thought the problem was still getting the process to run from a dataset.

If the problem is running multiple unloads in one step, suggest you create a small PROCedure and use a symbolic parameter for the member(s) to be used as well as the output dataset. Done correcly, the same symbolic can be used for both. Also, this can be run PROC & PEND if cataloging a PROC isn't wanted/needed.

Each EXEC of the PROC (all in the same job) would process one unload.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Dec 22, 2009 4:06 am    Post subject:
Reply with quote

Dick,

from his output, it appears that the TS wants to have all the queries in one sysin.
I thought that was not do-able. But the TS wants that and he has little or no concept of what you are trying to say. (no actually he is ignoring all indications that he can't do it that way)
Back to top
View user's profile Send private message
beto981

New User


Joined: 06 Oct 2006
Posts: 24

PostPosted: Wed Dec 23, 2009 12:47 am    Post subject:
Reply with quote

Tks Dick Brenholtz, your answers helped a lot!!!! You're right about what I want, but it's 'do-able'.

I found an easier way to do waht I want: to use DSNTEP2. The only problem is that the output can't have more than 133 bytes. With this, the search now is for a way to do the unload for a file with at least 350 bytes.

Dick scherrer, tks for your advice (for real!!)
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Wed Dec 23, 2009 12:56 am    Post subject: Reply to: How to run a query using INZUTILB
Reply with quote

Quote:
The only problem is that the output can't have more than 133 bytes. With this, the search now is for a way to do the unload for a file with at least 350 bytes.


only partially true.... if You look at the source in the sdsnsamp You will find all the instructions to modify dsntep2 in order to produce longer lines

see also
http://ibmmainframes.com/viewtopic.php?t=45308&highlight=dsntep2+source
Back to top
View user's profile Send private message
beto981

New User


Joined: 06 Oct 2006
Posts: 24

PostPosted: Wed Dec 23, 2009 1:09 am    Post subject:
Reply with quote

I didn't know that Enrico! Unfortunately I don't have permission to change the parameters of DSNTEP2.... I think I will have to work with page breaks.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Wed Dec 23, 2009 1:38 am    Post subject: Reply to: How to run a query using INZUTILB
Reply with quote

Quote:
Unfortunately I don't have permission to change the parameters of DSNTEP2


that' s a pretty standard practice,

make a copy of it change its name its plan names and use it as if it were a user written program

never met any objections to do it

why otherwise would IBM have done such a good job of documenting the mods icon_wink.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us