Build utility function requested: BUILD SYSPROC.DSNTPSMP - 8

HABBIE · New User Joined: 08 Jul 2014 Posts: 24 Location: INDIA

Hi,

I have connected to the mainframe machine using IBM data studio. I m trying to compile basic normal sql procedure. I m getting the below error.

CREATE PROCEDURE M671.SP_SCHM_LIST ()
RESULT SETS 5
LANGUAGE SQL
FENCED
COLLID TEST
WLM ENVIRONMENT DB8GENV1
RUN OPTIONS 'NOTEST(NONE,*,*,*)'
P1: BEGIN

DECLARE STMT1 varchar(1000);
---DECLARE LOG_PROCESS VARCHAR(4000);

SET STMT1 = ('INSERT INTO M671.DBLIST (SCHEMA_NAME)
(SELECT DISTINCT(CREATOR) AS SCHEMA_NAME FROM
SYSIBM.SYSTABLES WHERE CREATOR not like ''SYS%'')');

execute immediate STMT1;

END P1

Error:

Deploy M671.SP_SCHM_LIST

Running
M671.SP_SCHM_LIST - Deploy started.
Calling the build utility SYSPROC.DSNTPSMP...

CONVERT SQLPRINT 1 1DB2 SQL PRECOMPILER VERSION 8 REL. 1.0 PAGE 1
CONVERT SQLPRINT 2 0
CONVERT SQLPRINT 3 0OPTIONS SPECIFIED: STDSQL(NO) NEWFUN(YES) MAR(1,80) HOST(SQL) ONEPASS CCSID(37) MAR(1,80)
CONVERT SQLPRINT 4 0OPTIONS USED - SPECIFIED OR DEFAULTED
CONVERT SQLPRINT 5 APOST
CONVERT SQLPRINT 6 APOSTSQL
CONVERT SQLPRINT 7 ATTACH(TSO)
CONVERT SQLPRINT 8 CCSID(37)
CONVERT SQLPRINT 9 NOPADNTSTR
CONVERT SQLPRINT 10 CONNECT(2)
CONVERT SQLPRINT 11 DEC(15)
CONVERT SQLPRINT 12 FLAG(I)
CONVERT SQLPRINT 13 HOST(SQL)
CONVERT SQLPRINT 14 LINECOUNT(60)
CONVERT SQLPRINT 15 MARGINS(1,80)
CONVERT SQLPRINT 16 NEWFUN(YES)
CONVERT SQLPRINT 17 ONEPASS
CONVERT SQLPRINT 18 OPTIONS
CONVERT SQLPRINT 19 PERIOD
CONVERT SQLPRINT 20 NOSOURCE
CONVERT SQLPRINT 21 STDSQL(NO)
CONVERT SQLPRINT 22 SQL(DB2)
CONVERT SQLPRINT 23 NOXREF
CONVERT SQLPRINT 24 1DB2 SQL PRECOMPILER STATISTICS PAGE 2
CONVERT SQLPRINT 25 0SOURCE STATISTICS
CONVERT SQLPRINT 26 SOURCE LINES READ: 21
CONVERT SQLPRINT 27 NUMBER OF SYMBOLS: 5
CONVERT SQLPRINT 28 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 1424
CONVERT SQLPRINT 29 -THERE WERE 1 MESSAGES FOR THIS PROGRAM.
CONVERT SQLPRINT 30 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
CONVERT SQLPRINT 31 248152 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
CONVERT SQLPRINT 32 RETURN CODE IS 4
CONVERT SQLTERM 1 DSNH527I W DSNHOPTS THE PRECOMPILER ATTEMPTED TO USE THE DB2-SUPPLIED DSNHDECP MODULE
CONVERT SQLTERM 2 SOURCE STATISTICS
CONVERT SQLTERM 3 SOURCE LINES READ: 21
CONVERT SQLTERM 4 NUMBER OF SYMBOLS: 5
CONVERT SQLTERM 5 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 1424
CONVERT SQLTERM 6 THERE WERE 1 MESSAGES FOR THIS PROGRAM.
CONVERT SQLTERM 7 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
CONVERT SQLTERM 8 248152 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
CONVERT SQLTERM 9 RETURN CODE IS 4
DSNTPSMP CONSOLE 1 *** DB2 V8R1 SQL Procedure Processor DSNTPSMP 1.20 (DB2V8R1 2003-12-02)
DSNTPSMP CONSOLE 2 Language conversion warnings and/or errors, rc=4
DSNTPSMP CONSOLE 3 I/O error occured in "Precompile". Event: ALLOCATE DDNAME(PRCCIN) rc= 12
DSNTPSMP CONSOLE 4 Aborting due to I/O errors.
DSNTPSMP CONSOLE 5 ** WARNING ** Delete processing did not complete.
DSNTPSMP CONSOLE 6 Abend 213 occured while running dataset utility DSNX9STW.
DSNTPSMP Summary 1 Request: BUILD routine MENTIS1.SP_SCHM_LIST did not complete.

Build utility function requested: BUILD
SYSPROC.DSNTPSMP - Returned +8
M671.SP_SCHM_LIST - Deploy failed.
M671.SP_SCHM_LIST - Roll back completed successfully.

I don't understand what is this error. I don't know any admin activities on mainframe.
Can some one kindly guide me , what I have to do here .

My OS: Windows
db2 version : 8
Client : IBM data studio 4.1

Garry Carroll · Posted: Tue Aug 25, 2015 5:59 pm

HABBIE · New User Joined: 08 Jul 2014 Posts: 24 Location: INDIA

Thank you for the detailed.

Rohit Umarjikar · Posted: Thu Aug 27, 2015 12:09 am

Shouldn't ''SYS%'' be 'SYS%' ?