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

Error on Db2 z/os procedure


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

Senior Member


Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland

PostPosted: Fri Aug 28, 2015 7:42 pm
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: 1788
Location: Bloomington, IL

PostPosted: Fri Aug 28, 2015 8:45 pm
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: 792
Location: Pennsylvania

PostPosted: Sat Aug 29, 2015 12:29 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Error to read log with rexx CLIST & REXX 11
No new posts Error when install DB2 DB2 2
No new posts CLIST - Virtual storage allocation error CLIST & REXX 5
No new posts Error while running web tool kit REXX... CLIST & REXX 5
No new posts Getting Error while trying to establi... DB2 3
Search our Forums:

Back to Top