CALL SYSPROC.DSNUTILU from Db2 LUW Command Line Processor - DB21101E

Frank Fillmore, Jr.

CALL SYSPROC.DSNUTILU from Db2 LUW Command Line Processor - DB21101E

Hello Listers,

I have spent most of the day on this and I am just baffled.  I am trying to execute a Db2 for z/OS utility by calling SYSPROC.DSNUTILU from a Db2 for LUW Command Line Processor (CLP).

Here is the input file DSNUTILU-SP-STTBAUTH.sql (for testing)

call SYSPROC.DSNUTILU('HCDMONOT', 'NO',
'TEMPLATE STAUTH DSN (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,KEEP,KEEP)
 TEMPLATE UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL'
, ?);

I issue this from the Db2 for LUW CLP:

db2 -td; -vf DSNUTILU-SP-STTBAUTH.sql

Works like a champ:

call SYSPROC.DSNUTILU('HCDMONOT', 'NO', 'TEMPLATE STAUTH DSN (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,KEEP,KEEP) TEMPLATE UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL' , ?)
  RETCODE: 0


SEQNO       TEXT                                                                                                                                                            
1           1DSNU000I    211 16:56:19.21 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = HCDMONOT                                                                             
2            DSNU1045I   211 16:56:19.21 DSNUGTIS - PROCESSING SYSIN AS UNICODE UTF-8                                                                                       
3           0DSNU050I    211 16:56:19.21 DSNUGUTC -  TEMPLATE STAUTH DSN(PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,                                              
4            KEEP, KEEP)                                                                                                                                                    
5            DSNU1035I   211 16:56:19.21 DSNUJTDR - TEMPLATE STATEMENT PROCESSED SUCCESSFULLY                                                                               
6           0DSNU050I    211 16:56:19.21 DSNUGUTC -  TEMPLATE UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW, DELETE,                                                 
7            DELETE) UNIT 3390 SPACE(1, 1) CYL                                                                                                                              
8            DSNU1035I   211 16:56:19.21 DSNUJTDR - TEMPLATE STATEMENT PROCESSED SUCCESSFULLY                                                                               
9            DSNU010I    211 16:56:19.21 DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

However, if I add one more line it fails:

call SYSPROC.DSNUTILU('HCDMONOT', 'NO', 'TEMPLATE STAUTH DSN (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,KEEP,KEEP) TEMPLATE UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL TEMPLATE OUT DSN(PBTSO1.SYSOUT.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL' , ?)
DB21101E  Too few parameters were specified for stored procedure
"SYSPROC.DSNUTILU" (expected 4).

I can run a statement dozens of times longer in DataStudio and it works fine.  It looks like there is a limit to string length in the Db2 Command Line Processor (CLP).  Does anyone know?  Can it be changed?

Many thanks in advance.

Frank

David Simpson

CALL SYSPROC.DSNUTILU from Db2 LUW Command Line Processor - DB21101E [EXTERNAL]
(in response to Frank Fillmore, Jr.)
This is an indirect solution but I like to put my templates in the SYSTEMPL DD in the WLM address space where DSNUTILU is installed. This allows me to reference them using DSNUTILU without defining them each time. It would also shorten your control card.

From: Frank Fillmore, Jr. [mailto:[login to unmask email]
Sent: Wednesday, July 29, 2020 3:44 PM
To: [login to unmask email]
Subject: [DB2-L] - CALL SYSPROC.DSNUTILU from Db2 LUW Command Line Processor - DB21101E [EXTERNAL]


Hello Listers,

I have spent most of the day on this and I am just baffled. I am trying to execute a Db2 for z/OS utility by calling SYSPROC.DSNUTILU from a Db2 for LUW Command Line Processor (CLP).

Here is the input file DSNUTILU-SP-STTBAUTH.sql (for testing)

call SYSPROC.DSNUTILU('HCDMONOT', 'NO',
'TEMPLATE STAUTH DSN (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,KEEP,KEEP)
TEMPLATE UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL'
, ?);

I issue this from the Db2 for LUW CLP:

db2 -td; -vf DSNUTILU-SP-STTBAUTH.sql

Works like a champ:

call SYSPROC.DSNUTILU('HCDMONOT', 'NO', 'TEMPLATE STAUTH DSN (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,KEEP,KEEP) TEMPLATE UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL' , ?)
RETCODE: 0


SEQNO TEXT
1 1DSNU000I 211 16:56:19.21 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = HCDMONOT
2 DSNU1045I 211 16:56:19.21 DSNUGTIS - PROCESSING SYSIN AS UNICODE UTF-8
3 0DSNU050I 211 16:56:19.21 DSNUGUTC - TEMPLATE STAUTH DSN(PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,
4 KEEP, KEEP)
5 DSNU1035I 211 16:56:19.21 DSNUJTDR - TEMPLATE STATEMENT PROCESSED SUCCESSFULLY
6 0DSNU050I 211 16:56:19.21 DSNUGUTC - TEMPLATE UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW, DELETE,
7 DELETE) UNIT 3390 SPACE(1, 1) CYL
8 DSNU1035I 211 16:56:19.21 DSNUJTDR - TEMPLATE STATEMENT PROCESSED SUCCESSFULLY
9 DSNU010I 211 16:56:19.21 DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

However, if I add one more line it fails:

call SYSPROC.DSNUTILU('HCDMONOT', 'NO', 'TEMPLATE STAUTH DSN (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,KEEP,KEEP) TEMPLATE UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL TEMPLATE OUT DSN(PBTSO1.SYSOUT.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL' , ?)
DB21101E Too few parameters were specified for stored procedure
"SYSPROC.DSNUTILU" (expected 4).

I can run a statement dozens of times longer in DataStudio and it works fine. It looks like there is a limit to string length in the Db2 Command Line Processor (CLP). Does anyone know? Can it be changed?

Many thanks in advance.

Frank

-----End Original Message-----
________________________________
Please note: This message originated outside your organization. Please use caution when opening links or attachments.

Philip Nelson

CALL SYSPROC.DSNUTILU from Db2 LUW Command Line Processor - DB21101E
(in response to Frank Fillmore, Jr.)
Frank,

Have been experimenting with DSNUTILU myself recently.

I've been running them using both CLP and CLPPLUS, and much longer than
what you have specified without an issue.

I have not only a bunch of templates but also a fairly large LOAD command
in there (getting that working is another story in itself, since DSNUTILU
is defined with SECURITY USER and you need to give the user executing
DSNUTILU update access to the JES spool for the WLM started task, so that
it can pass back the outputs from any SORT steps to the result set).

I've had similar things to you before. It typically happens when you've
confused CLP (or Data Studio) into thinking that the ending quote of a
parameter isn't the end. Unfortunately I've no idea why ... so I'm not
being the most helpful here.

You could try pushing it through CLPPLUS rather than CLP and see if that
works for you?

Phil



On Wed, 29 Jul 2020 at 22:44, Frank Fillmore, Jr. <[login to unmask email]>
wrote:

> Hello Listers,
>
> I have spent most of the day on this and I am just baffled. I am trying
> to execute a Db2 for z/OS utility by calling SYSPROC.DSNUTILU from a Db2
> for LUW Command Line Processor (CLP).
>
> Here is the input file *DSNUTILU-SP-STTBAUTH.sql* (for testing)
>
> *call SYSPROC.DSNUTILU('HCDMONOT', 'NO',*
> *'TEMPLATE STAUTH DSN (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS)
> DISP(OLD,KEEP,KEEP)*
> * TEMPLATE UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.)
> DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL'*
> *, ?);*
>
> I issue this from the Db2 for LUW CLP:
>
>
> *db2 -td; -vf DSNUTILU-SP-STTBAUTH.sql*
> Works like a champ:
>
> *call SYSPROC.DSNUTILU('HCDMONOT', 'NO', 'TEMPLATE STAUTH DSN
> (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,KEEP,KEEP) TEMPLATE
> UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT
> 3390 SPACE(1,1) CYL' , ?)*
> * RETCODE: 0*
>
>
> *SEQNO
> TEXT
> *
> *1 1DSNU000I 211 16:56:19.21 DSNUGUTC - OUTPUT START FOR
> UTILITY, UTILID =
> HCDMONOT
> *
> *2 DSNU1045I 211 16:56:19.21 DSNUGTIS - PROCESSING SYSIN AS
> UNICODE
> UTF-8
> *
> *3 0DSNU050I 211 16:56:19.21 DSNUGUTC - TEMPLATE STAUTH
> DSN(PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS)
> DISP(OLD, *
> *4 KEEP,
> KEEP)
> *
> *5 DSNU1035I 211 16:56:19.21 DSNUJTDR - TEMPLATE STATEMENT
> PROCESSED
> SUCCESSFULLY
> *
> *6 0DSNU050I 211 16:56:19.21 DSNUGUTC - TEMPLATE UT1
> DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,
> DELETE, *
> *7 DELETE) UNIT 3390 SPACE(1, 1)
> CYL
> *
> *8 DSNU1035I 211 16:56:19.21 DSNUJTDR - TEMPLATE STATEMENT
> PROCESSED
> SUCCESSFULLY
> *
> *9 DSNU010I 211 16:56:19.21 DSNUGBAC - UTILITY EXECUTION
> COMPLETE, HIGHEST RETURN CODE=0*
>
> However, if I add one more line it fails:
>
> call SYSPROC.DSNUTILU('HCDMONOT', 'NO', 'TEMPLATE STAUTH DSN
> (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,KEEP,KEEP) TEMPLATE
> UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT
> 3390 SPACE(1,1) CYL TEMPLATE OUT DSN(PBTSO1.SYSOUT.HCDMONOT.D&JU..T&TI.)
> DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL' , ?)
> *DB21101E Too few parameters were specified for stored procedure*
> *"SYSPROC.DSNUTILU" (expected 4).*
>
> I can run a statement dozens of times longer in DataStudio and it works
> fine. It looks like there is a limit to string length in the Db2 Command
> Line Processor (CLP). Does anyone know? Can it be changed?
>
> Many thanks in advance.
>
> Frank
>
> -----End Original Message-----
>

James Campbell

CALL SYSPROC.DSNUTILU from Db2 LUW Command Line Processor - DB21101E
(in response to Philip Nelson)
I've never done this, but as a "this looks like another way" - DRDA Fast Load:

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/admin/src/tpc/db2z_drdaf
astloadzload.html

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cm
d.doc/doc/r0070172.html

James Campbell


On 29 Jul 2020 at 23:06, Philip Nelson wrote:

> Frank,
>
> Have been experimenting with DSNUTILU myself recently.
>
> I've been running them using both CLP and CLPPLUS, and much longer than
> what you have specified without an issue.
>
> I have not only a bunch of templates but also a fairly large LOAD command
> in there (getting that working is another story in itself, since DSNUTILU
> is defined with SECURITY USER and you need to give the user executing
> DSNUTILU update access to the JES spool for the WLM started task, so that
> it can pass back the outputs from any SORT steps to the result set).
>
> I've had similar things to you before. It typically happens when you've
> confused CLP (or Data Studio) into thinking that the ending quote of a
> parameter isn't the end. Unfortunately I've no idea why ... so I'm not
> being the most helpful here.
>
> You could try pushing it through CLPPLUS rather than CLP and see if that
> works for you?
>
> Phil
>
>
>
> On Wed, 29 Jul 2020 at 22:44, Frank Fillmore, Jr. <[login to unmask email]>
> wrote:
>
> > Hello Listers,
> >
> > I have spent most of the day on this and I am just baffled. I am trying
> > to execute a Db2 for z/OS utility by calling SYSPROC.DSNUTILU from a Db2
> > for LUW Command Line Processor (CLP).
> >
> > Here is the input file *DSNUTILU-SP-STTBAUTH.sql* (for testing)
> >
> > *call SYSPROC.DSNUTILU('HCDMONOT', 'NO',*
> > *'TEMPLATE STAUTH DSN (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS)
> > DISP(OLD,KEEP,KEEP)*
> > * TEMPLATE UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.)
> > DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL'*
> > *, ?);*
> >
> > I issue this from the Db2 for LUW CLP:
> >
> >
> > *db2 -td; -vf DSNUTILU-SP-STTBAUTH.sql*
> > Works like a champ:
> >
> > *call SYSPROC.DSNUTILU('HCDMONOT', 'NO', 'TEMPLATE STAUTH DSN
> > (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,KEEP,KEEP) TEMPLATE
> > UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT
> > 3390 SPACE(1,1) CYL' , ?)*
> > * RETCODE: 0*
> >
> >
> > *SEQNO
> > TEXT
> > *
> > *1 1DSNU000I 211 16:56:19.21 DSNUGUTC - OUTPUT START FOR
> > UTILITY, UTILID =
> > HCDMONOT
> > *
> > *2 DSNU1045I 211 16:56:19.21 DSNUGTIS - PROCESSING SYSIN AS
> > UNICODE
> > UTF-8
> > *
> > *3 0DSNU050I 211 16:56:19.21 DSNUGUTC - TEMPLATE STAUTH
> > DSN(PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS)
> > DISP(OLD, *
> > *4 KEEP,
> > KEEP)
> > *
> > *5 DSNU1035I 211 16:56:19.21 DSNUJTDR - TEMPLATE STATEMENT
> > PROCESSED
> > SUCCESSFULLY
> > *
> > *6 0DSNU050I 211 16:56:19.21 DSNUGUTC - TEMPLATE UT1
> > DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,
> > DELETE, *
> > *7 DELETE) UNIT 3390 SPACE(1, 1)
> > CYL
> > *
> > *8 DSNU1035I 211 16:56:19.21 DSNUJTDR - TEMPLATE STATEMENT
> > PROCESSED
> > SUCCESSFULLY
> > *
> > *9 DSNU010I 211 16:56:19.21 DSNUGBAC - UTILITY EXECUTION
> > COMPLETE, HIGHEST RETURN CODE=0*
> >
> > However, if I add one more line it fails:
> >
> > call SYSPROC.DSNUTILU('HCDMONOT', 'NO', 'TEMPLATE STAUTH DSN
> > (PBTSO16.HLD0001.HCDMONOT.WORK.SRC.SSHLDMWS) DISP(OLD,KEEP,KEEP) TEMPLATE
> > UT1 DSN(PBTSO1.SYSUT1.HCDMONOT.D&JU..T&TI.) DISP(NEW,DELETE,DELETE) UNIT
> > 3390 SPACE(1,1) CYL TEMPLATE OUT DSN(PBTSO1.SYSOUT.HCDMONOT.D&JU..T&TI.)
> > DISP(NEW,DELETE,DELETE) UNIT 3390 SPACE(1,1) CYL' , ?)
> > *DB21101E Too few parameters were specified for stored procedure*
> > *"SYSPROC.DSNUTILU" (expected 4).*
> >
> > I can run a statement dozens of times longer in DataStudio and it works
> > fine. It looks like there is a limit to string length in the Db2 Command
> > Line Processor (CLP). Does anyone know? Can it be changed?
> >
> > Many thanks in advance.
> >
> > Frank
> >
> > -----End Original Message-----
> >


--
This email has been checked for viruses by AVG.
https://www.avg.com