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

How to run a query using INZUTILB


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Dec 18, 2009 8:40 pm
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
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
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

Moderator Emeritus


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

PostPosted: Fri Dec 18, 2009 10:24 pm
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
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
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

Moderator Emeritus


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

PostPosted: Tue Dec 22, 2009 1:12 am
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
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

Moderator Emeritus


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

PostPosted: Tue Dec 22, 2009 1:36 am
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
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

Moderator Emeritus


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

PostPosted: Tue Dec 22, 2009 3:04 am
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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Dec 23, 2009 12:56 am
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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Dec 23, 2009 1:38 am
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
beto981

New User


Joined: 06 Oct 2006
Posts: 24

PostPosted: Wed Dec 23, 2009 1:45 am
Reply with quote

Copy made. Sorry for the stupid question, but how (where) do I change it now?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Dec 23, 2009 1:51 am
Reply with quote

read the source and You will find out by Yourself

here is a hint anyway ...
Code:
  *           PAGEWIDTH   133    Maximum width of a page in          *  03050000
  *                              characters (including the control   *  03060000
  *                              character in column one)            *  03070000
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Wed Dec 23, 2009 1:53 am
Reply with quote

Hello,

And the modified version of the code needs to be in an available library.

You may not have permission to write the copy into the same dataset that contains the original. . .
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:57 am
Reply with quote

No, I mean where (PDF,DB2,Insync,etc) can I do the changes? I made, but opening in the PDF I just saw this:

Code:
  XXXXXXXXXX(DSNTEP2)                  Browse substituted
Command ===>                                                  Scroll ===> CSR 
******************************** Top of Data **********************************
......0CEESTART........CEEMAIN ........CEEFMAIN......  CEEBETBL........CEEROOTA
......0DSNTEP2A........DSNTEP28........IELCGIX ... ...QIELCGIB ....... IELCGOG
......0IBMSBGKC........IBMSCACA...\....IBMSCCAA...H....IBMSCCCA........IBMSCCQA
......0IBMSCHFE........IBMSCHFH........IBMSCHFP...-....IBMSCHFY........IBMSCHXD
......0IBMSCTHP... ....IBMSCTHX........IBMSCUID........IBMSCUIE........IBMSCUIF
....<.0IBMSSAIA........IBMSSAOA........IBMSSEDB...&....IBMSSEIA...-....IBMSSEIT
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Dec 23, 2009 3:20 am
Reply with quote

what I posted was a pointer on how to modify the source off dsntep2 in order to adapt it to Your needs.
You are not changing the invocation parameters You are modifying the source in order to create a new program

You have to behave as if it was a program written by you

copy the source from <db2hlq>.sdsnsamp to a library of Your choice,
modify the line I showed You
precompile/compile/link to a load available at run time
bind the dbrm to the proper DB2 subsystem ... and there You are up and running with a modified dsntep2
remember to use a different name
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 Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top