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
 

 

Error on Db2 z/os procedure

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
HABBIE

New User


Joined: 08 Jul 2014
Posts: 24
Location: INDIA

PostPosted: Fri Aug 28, 2015 7:00 pm    Post subject: Error on Db2 z/os procedure
Reply with quote

Hi,

I am very new to Z/OS stored procedures.
I m trying to create a procedure on my machine

CREATE PROCEDURE SP_LOG_MENTIS
(IN V_JOB_ID INTEGER,IN LOG_PROCESS VARCHAR(100))
RESULT SETS 5
LANGUAGE SQL
FENCED
COLLID TEST
WLM ENVIRONMENT DB8GENV1
RUN OPTIONS 'NOTEST(NONE,*,*,*)'
P1: BEGIN

DECLARE VAR1 VARCHAR(2000);
DECLARE VAR2 INTEGER;
DECLARE VAR3 VARCHAR(50);
DECLARE FINAL_LOG VARCHAR(2000);

SET VAR2 = (SELECT MAX(LINE_NUM) FROM FR_JOB_LOGS
WHERE JOB_ID = V_JOB_ID);

IF (VAR2 = 0 )
THEN
SET VAR2=1;
ELSE
SET VAR2 = VAR2+1;
END IF;

SET VAR3= (SELECT CURRENT TIMESTAMP AS CURRENT_SERVER_TIME
FROM SYSIBM.SYSDUMMY1);
SET FINAL_LOG= (LOG_PROCESS CONCAT ' @----- ' CONCAT VAR3);

SET VAR1='INSERT INTO FR_JOB_LOGS (JOB_ID,LINE_NUM,LOG)
values ('|| V_JOB_ID ||','|| VAR2 || ','''
|| FINAL_LOG || ''')';
execute immediate VAR1;


END P1

Error:

Deploy MENTIS1.SP_LOG_MENTIS(INTEGER, VARCHAR(100))

Running
MENTIS1.SP_LOG_MENTIS - Deploy for debug started.
Calling the build utility SYSPROC.DSNTPSMP...



BIND CONSOLE 1 DSNX200I -DB8G BIND SQL ERROR
BIND CONSOLE 2 USING MENTIS1 AUTHORITY
BIND CONSOLE 3 PLAN=(NOT APPLICABLE)
BIND CONSOLE 4 DBRM=SQL41279
BIND CONSOLE 5 STATEMENT=458
BIND CONSOLE 6 SQLCODE=-171
BIND CONSOLE 7 SQLSTATE=42815
BIND CONSOLE 8 TOKENS=2Ÿ||
BIND CONSOLE 9 CSECT NAME=DSNXOBFA
BIND CONSOLE 10 RDS CODE=30
BIND CONSOLE 11 DSNT233I -DB8G UNSUCCESSFUL BIND FOR
BIND CONSOLE 12 PACKAGE = DALLAS8.TEST.SQL41279.()
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) SQLDEBUG(PSM)
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: 36
CONVERT SQLPRINT 27 NUMBER OF SYMBOLS: 16
CONVERT SQLPRINT 28 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 2160
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 256152 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: 36
CONVERT SQLTERM 4 NUMBER OF SYMBOLS: 16
CONVERT SQLTERM 5 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 2160
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 256152 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 Precompile warnings and/or errors, rc=4
DSNTPSMP CONSOLE 4 BIND PACKAGE warnings and/or errors, rc=8
DSNTPSMP CONSOLE 5 BIND PACKAGE is not successful, cannot continue.
DSNTPSMP Summary 1 Request: BUILD_DEBUG routine SP_LOG_MENTIS did not complete.
PRECOMPILE SQLPRINT 1 1DB2 SQL PRECOMPILER VERSION 8 REL. 1.0 PAGE 1
PRECOMPILE SQLPRINT 2 0
PRECOMPILE SQLPRINT 3 0OPTIONS SPECIFIED: STDSQL(NO) TWOPASS NEWFUN(YES) MAR(1,80) HOST(C) MAR(1,80) CCSID(1047) ATTACH(RRSAF)
PRECOMPILE SQLPRINT 4 0OPTIONS USED - SPECIFIED OR DEFAULTED
PRECOMPILE SQLPRINT 5 APOST
PRECOMPILE SQLPRINT 6 APOSTSQL
PRECOMPILE SQLPRINT 7 ATTACH(RRSAF)
PRECOMPILE SQLPRINT 8 CCSID(1047)
PRECOMPILE SQLPRINT 9 NOPADNTSTR
PRECOMPILE SQLPRINT 10 CONNECT(2)
PRECOMPILE SQLPRINT 11 DEC(15)
PRECOMPILE SQLPRINT 12 FLAG(I)
PRECOMPILE SQLPRINT 13 HOST(C)
PRECOMPILE SQLPRINT 14 FLOAT(S390)
PRECOMPILE SQLPRINT 15 LINECOUNT(60)
PRECOMPILE SQLPRINT 16 MARGINS(1,80)
PRECOMPILE SQLPRINT 17 NEWFUN(YES)
PRECOMPILE SQLPRINT 18 OPTIONS
PRECOMPILE SQLPRINT 19 PERIOD
PRECOMPILE SQLPRINT 20 NOSOURCE
PRECOMPILE SQLPRINT 21 STDSQL(NO)
PRECOMPILE SQLPRINT 22 SQL(DB2)
PRECOMPILE SQLPRINT 23 TWOPASS
PRECOMPILE SQLPRINT 24 NOXREF
PRECOMPILE SQLPRINT 25 1DB2 SQL PRECOMPILER STATISTICS PAGE 2
PRECOMPILE SQLPRINT 26 0SOURCE STATISTICS
PRECOMPILE SQLPRINT 27 SOURCE LINES READ: 527
PRECOMPILE SQLPRINT 28 NUMBER OF SYMBOLS: 44
PRECOMPILE SQLPRINT 29 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 4096
PRECOMPILE SQLPRINT 30 -THERE WERE 1 MESSAGES FOR THIS PROGRAM.
PRECOMPILE SQLPRINT 31 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
PRECOMPILE SQLPRINT 32 180344 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
PRECOMPILE SQLPRINT 33 RETURN CODE IS 4
PRECOMPILE SQLTERM 1 DSNH527I W DSNHOPTS THE PRECOMPILER ATTEMPTED TO USE THE DB2-SUPPLIED DSNHDECP MODULE
PRECOMPILE SQLTERM 2 SOURCE STATISTICS
PRECOMPILE SQLTERM 3 SOURCE LINES READ: 527
PRECOMPILE SQLTERM 4 NUMBER OF SYMBOLS: 44
PRECOMPILE SQLTERM 5 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 4096
PRECOMPILE SQLTERM 6 THERE WERE 1 MESSAGES FOR THIS PROGRAM.
PRECOMPILE SQLTERM 7 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
PRECOMPILE SQLTERM 8 180344 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
PRECOMPILE SQLTERM 9 RETURN CODE IS 4


Build utility function requested: BUILD_DEBUG
SYSPROC.DSNTPSMP - Returned +8
MENTIS1.SP_LOG_MENTIS - Deploy for debug failed.
MENTIS1.SP_LOG_MENTIS - Roll back completed successfully.


I don't understand anything from this error log. I request kindly give me some hint , on what I m missing here.

OS: Windows
Db2 : version 8
Client:IBM data studio
Back to top
View user's profile Send private message

Garry Carroll

Active Member


Joined: 08 May 2006
Posts: 988
Location: Dublin, Ireland / Edinburgh, Scotland

PostPosted: Fri Aug 28, 2015 7:42 pm    Post subject:
Reply with quote

Judging by the level of your questions and the fact that you don't seem to be able (or willing) to find the error messages, or to use the 'code' tags where appropriate, I think you are in the wrong forum - you likely need a beginners' forum ....

The relevant message this time would seem to be :
Code:
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 Precompile warnings and/or errors, rc=4
 DSNTPSMP CONSOLE 4 BIND PACKAGE warnings and/or errors, rc=8
 DSNTPSMP CONSOLE 5 BIND PACKAGE is not successful, cannot continue.


Garry.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Fri Aug 28, 2015 8:45 pm    Post subject:
Reply with quote

I would also point out the negative SQLCODE on the BIND CONSOLE 6 line, and urge the TS to look it up in the fine manual.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Sat Aug 29, 2015 12:29 am    Post subject:
Reply with quote

Quote:
Judging by the level of your questions and the fact that you don't seem to be able (or willing) to find the error messages, or to use the 'code' tags where appropriate, I think you are in the wrong forum - you likely need a beginners' forum ....


This poster has been nursed along for a few weeks.

His company obviously has not provided the proper training and support for him to properly do his job.

Why should they, he can get what he needs from here.
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Error during restore rename archanamuthukrishnan All Other Mainframe Topics 2 Fri Oct 14, 2016 3:30 pm


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