Db2 z/os NSQL proc Dynamic global table insert

Robert Krall

Db2 z/os NSQL proc Dynamic global table insert

Hello,

I am trying to get a very simple dynamic native stored procedure to work on db2 for z/OS and i can't seem to figure it out why it wont work. I dont get an error message, but it never inserts rows into my table.  I have granted publi crole to insert into this table.  Does anyone have any ideas?  Seems to be fine I dont use the global temp table, but I need to be able to use that.  Thanks for your help!  All my reading of documentation and forums does not talk about global temp table inside dynamic sql so I am lost to why its not working.

Here is the DDL:

CREATE PROCEDURE Schema1.NativeDynamicProc (
OUT "@O_SQLCODE" INTEGER,
out @O_SqlState varchar(500),
OUT "@O_MSGTEXT" VARCHAR(500) FOR SBCS DATA CCSID EBCDIC,
OUT "@O_ERRORLINENUMBER" INTEGER
)
VERSION V1
LANGUAGE SQL
Result SETS 1
dynamicrules bind
CALLED ON NULL INPUT
MODIFIES SQL DATA
with keep dynamic
P1:
BEGIN

Declare @DSTRING varchar(500);

DECLARE C2 CURSOR FOR S1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DSTRING = 'drop table SESSION.tbl1; '
||'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;'
|| 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from Schema1.TestDelTable where Code < 3; ';

PREPARE S1 FROM @DSTRING;
open C2;
EXECUTE S1;
commit;
--close C2;
END P1#

 

 

 

 

 

 

 

 

Philip Sevetson

Db2 z/os NSQL proc Dynamic global table insert
(in response to Robert Krall)
**please note my email address change**
Robert,

I’m _not_ an expert on Native SQL Stored Procedures (or any other kind).

That said, you have a DROP, a CREATE, and an INSERT on SESSION.tbl1 concatenated into a single execution – are you getting three distinct SQLCODEs on that? If so, are they all SQLCODE=0?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:41 AM
To: [login to unmask email]
Subject: [DB2-L] - Db2 z/os NSQL proc Dynamic global table insert


Hello,

I am trying to get a very simple dynamic native stored procedure to work on db2 for z/OS and i can't seem to figure it out why it wont work. I dont get an error message, but it never inserts rows into my table. I have granted publi crole to insert into this table. Does anyone have any ideas? Seems to be fine I dont use the global temp table, but I need to be able to use that. Thanks for your help! All my reading of documentation and forums does not talk about global temp table inside dynamic sql so I am lost to why its not working.

Here is the DDL:

CREATE PROCEDURE Schema1.NativeDynamicProc (
OUT "@O_SQLCODE" INTEGER,
out @O_SqlState varchar(500),
OUT "@O_MSGTEXT" VARCHAR(500) FOR SBCS DATA CCSID EBCDIC,
OUT "@O_ERRORLINENUMBER" INTEGER
)
VERSION V1
LANGUAGE SQL
Result SETS 1
dynamicrules bind
CALLED ON NULL INPUT
MODIFIES SQL DATA
with keep dynamic
P1:
BEGIN

Declare @DSTRING varchar(500);

DECLARE C2 CURSOR FOR S1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DSTRING = 'drop table SESSION.tbl1; '
||'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;'
|| 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from Schema1.TestDelTable where Code < 3; ';

PREPARE S1 FROM @DSTRING;
open C2;
EXECUTE S1;
commit;
--close C2;
END P1#

















-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Robert Krall

Db2 z/os NSQL proc Dynamic global table insert
(in response to Philip Sevetson)
I am not getting any sql codes:

[cid:[login to unmask email]

I have the drop cause DB2 will not auto drop the table after you execute the proc.

So I created the continue handler to ignore when dropping an object that does not exists and to ignore creating an object that is already there.

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 9:54 AM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
Robert,

I’m _not_ an expert on Native SQL Stored Procedures (or any other kind).

That said, you have a DROP, a CREATE, and an INSERT on SESSION.tbl1 concatenated into a single execution – are you getting three distinct SQLCODEs on that? If so, are they all SQLCODE=0?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:41 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 z/os NSQL proc Dynamic global table insert


Hello,

I am trying to get a very simple dynamic native stored procedure to work on db2 for z/OS and i can't seem to figure it out why it wont work. I dont get an error message, but it never inserts rows into my table. I have granted publi crole to insert into this table. Does anyone have any ideas? Seems to be fine I dont use the global temp table, but I need to be able to use that. Thanks for your help! All my reading of documentation and forums does not talk about global temp table inside dynamic sql so I am lost to why its not working.

Here is the DDL:

CREATE PROCEDURE Schema1.NativeDynamicProc (
OUT "@O_SQLCODE" INTEGER,
out @O_SqlState varchar(500),
OUT "@O_MSGTEXT" VARCHAR(500) FOR SBCS DATA CCSID EBCDIC,
OUT "@O_ERRORLINENUMBER" INTEGER
)
VERSION V1
LANGUAGE SQL
Result SETS 1
dynamicrules bind
CALLED ON NULL INPUT
MODIFIES SQL DATA
with keep dynamic
P1:
BEGIN

Declare @DSTRING varchar(500);

DECLARE C2 CURSOR FOR S1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DSTRING = 'drop table SESSION.tbl1; '
||'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;'
|| 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from Schema1.TestDelTable where Code < 3; ';

PREPARE S1 FROM @DSTRING;
open C2;
EXECUTE S1;
commit;
--close C2;
END P1#

















-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
Attachments

  • image002.png (4.9k)
  • image003.png (3.3k)

Philip Sevetson

Db2 z/os NSQL proc Dynamic global table insert
(in response to Robert Krall)
**please note my email address change**
If this were REXX, I could tell you to execute TRACE I;. The best I can suggest right now for this is to break up the statement and obtain SQLCODE after each. (Also, check to see if the statement still doesn’t work when broken up, but that’s just due diligence to see that the test is valid.)

I don’t have a clue what’s wrong here, but going through and checking results after each operation should at least give you a +100 SQLCODE if there’s no data. (Although it seems to me like there has to be something wrong before the INSERT/SELECT, or a problem between the end of the SQL and the presentation of the return cursor.)

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 11:02 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I am not getting any sql codes:

[cid:[login to unmask email]

I have the drop cause DB2 will not auto drop the table after you execute the proc.

So I created the continue handler to ignore when dropping an object that does not exists and to ignore creating an object that is already there.

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 9:54 AM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
Robert,

I’m _not_ an expert on Native SQL Stored Procedures (or any other kind).

That said, you have a DROP, a CREATE, and an INSERT on SESSION.tbl1 concatenated into a single execution – are you getting three distinct SQLCODEs on that? If so, are they all SQLCODE=0?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:41 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 z/os NSQL proc Dynamic global table insert


Hello,

I am trying to get a very simple dynamic native stored procedure to work on db2 for z/OS and i can't seem to figure it out why it wont work. I dont get an error message, but it never inserts rows into my table. I have granted publi crole to insert into this table. Does anyone have any ideas? Seems to be fine I dont use the global temp table, but I need to be able to use that. Thanks for your help! All my reading of documentation and forums does not talk about global temp table inside dynamic sql so I am lost to why its not working.

Here is the DDL:

CREATE PROCEDURE Schema1.NativeDynamicProc (
OUT "@O_SQLCODE" INTEGER,
out @O_SqlState varchar(500),
OUT "@O_MSGTEXT" VARCHAR(500) FOR SBCS DATA CCSID EBCDIC,
OUT "@O_ERRORLINENUMBER" INTEGER
)
VERSION V1
LANGUAGE SQL
Result SETS 1
dynamicrules bind
CALLED ON NULL INPUT
MODIFIES SQL DATA
with keep dynamic
P1:
BEGIN

Declare @DSTRING varchar(500);

DECLARE C2 CURSOR FOR S1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DSTRING = 'drop table SESSION.tbl1; '
||'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;'
|| 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from Schema1.TestDelTable where Code < 3; ';

PREPARE S1 FROM @DSTRING;
open C2;
EXECUTE S1;
commit;
--close C2;
END P1#

















-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Daniel Luksetich

Db2 z/os NSQL proc Dynamic global table insert
(in response to Robert Krall)
I’ve never seen that done on DB2 for z/OS where you have multiple statements in a single execution. On Db2 for LUW you can create a compound statement, but even for that you can’t open a cursor on an insert. Why not just code without the dynamic SQL? One statement at a time.

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:02 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert



I am not getting any sql codes:







I have the drop cause DB2 will not auto drop the table after you execute the proc.



So I created the continue handler to ignore when dropping an object that does not exists and to ignore creating an object that is already there.



Thank you,

Robert Krall

x8725



From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 9:54 AM
To: '[login to unmask email]' <[login to unmask email] <mailto:[login to unmask email]> >
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert



**please note my email address change**

Robert,



I’m _not_ an expert on Native SQL Stored Procedures (or any other kind).



That said, you have a DROP, a CREATE, and an INSERT on SESSION.tbl1 concatenated into a single execution – are you getting three distinct SQLCODEs on that? If so, are they all SQLCODE=0?



Philip Sevetson

Computer Systems Manager

450 West 33rd Street

New York, NY 10001

212-857-1688 w

917-991-7052 c

212-857-1659 f





From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:41 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 z/os NSQL proc Dynamic global table insert



Hello,

I am trying to get a very simple dynamic native stored procedure to work on db2 for z/OS and i can't seem to figure it out why it wont work. I dont get an error message, but it never inserts rows into my table. I have granted publi crole to insert into this table. Does anyone have any ideas? Seems to be fine I dont use the global temp table, but I need to be able to use that. Thanks for your help! All my reading of documentation and forums does not talk about global temp table inside dynamic sql so I am lost to why its not working.

Here is the DDL:

CREATE PROCEDURE Schema1.NativeDynamicProc (
OUT "@O_SQLCODE" INTEGER,
out @O_SqlState varchar(500),
OUT "@O_MSGTEXT" VARCHAR(500) FOR SBCS DATA CCSID EBCDIC,
OUT "@O_ERRORLINENUMBER" INTEGER
)
VERSION V1
LANGUAGE SQL
Result SETS 1
dynamicrules bind
CALLED ON NULL INPUT
MODIFIES SQL DATA
with keep dynamic
P1:
BEGIN

Declare @DSTRING varchar(500);

DECLARE C2 CURSOR FOR S1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DSTRING = 'drop table SESSION.tbl1; '
||'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;'
|| 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from Schema1.TestDelTable where Code < 3; ';

PREPARE S1 FROM @DSTRING;
open C2;
EXECUTE S1;
commit;
--close C2;
END P1#



















-----End Original Message-----

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----



-----End Original Message-----

Robert Krall

Db2 z/os NSQL proc Dynamic global table insert
(in response to Daniel Luksetich)
Sure. I might be out of luck then.

I am trying to delete rows from several tables and instead of creating 30+ procs I wanted to do it with dynamic sql and use one table.

Thank you,
Robert Krall
x8725

From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:25 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I’ve never seen that done on DB2 for z/OS where you have multiple statements in a single execution. On Db2 for LUW you can create a compound statement, but even for that you can’t open a cursor on an insert. Why not just code without the dynamic SQL? One statement at a time.
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Chairman
IBM Certified Database Adminstrator – DB2 11 DBA for z/OS
IBM Certified System Administrator – DB2 11 for z/OS
IBM Certified Application Developer – DB2 11 for z/OS
IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:02 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I am not getting any sql codes:

[cid:[login to unmask email]

I have the drop cause DB2 will not auto drop the table after you execute the proc.

So I created the continue handler to ignore when dropping an object that does not exists and to ignore creating an object that is already there.

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 9:54 AM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
Robert,

I’m _not_ an expert on Native SQL Stored Procedures (or any other kind).

That said, you have a DROP, a CREATE, and an INSERT on SESSION.tbl1 concatenated into a single execution – are you getting three distinct SQLCODEs on that? If so, are they all SQLCODE=0?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:41 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 z/os NSQL proc Dynamic global table insert


Hello,

I am trying to get a very simple dynamic native stored procedure to work on db2 for z/OS and i can't seem to figure it out why it wont work. I dont get an error message, but it never inserts rows into my table. I have granted publi crole to insert into this table. Does anyone have any ideas? Seems to be fine I dont use the global temp table, but I need to be able to use that. Thanks for your help! All my reading of documentation and forums does not talk about global temp table inside dynamic sql so I am lost to why its not working.

Here is the DDL:

CREATE PROCEDURE Schema1.NativeDynamicProc (
OUT "@O_SQLCODE" INTEGER,
out @O_SqlState varchar(500),
OUT "@O_MSGTEXT" VARCHAR(500) FOR SBCS DATA CCSID EBCDIC,
OUT "@O_ERRORLINENUMBER" INTEGER
)
VERSION V1
LANGUAGE SQL
Result SETS 1
dynamicrules bind
CALLED ON NULL INPUT
MODIFIES SQL DATA
with keep dynamic
P1:
BEGIN

Declare @DSTRING varchar(500);

DECLARE C2 CURSOR FOR S1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DSTRING = 'drop table SESSION.tbl1; '
||'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;'
|| 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from Schema1.TestDelTable where Code < 3; ';

PREPARE S1 FROM @DSTRING;
open C2;
EXECUTE S1;
commit;
--close C2;
END P1#

















-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

Philip Sevetson

Db2 z/os NSQL proc Dynamic global table insert
(in response to Robert Krall)
**please note my email address change**
I don’t understand your conclusion. Why not execute the three statements distinctly within the one stored procedure?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 12:54 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

Sure. I might be out of luck then.

I am trying to delete rows from several tables and instead of creating 30+ procs I wanted to do it with dynamic sql and use one table.

Thank you,
Robert Krall
x8725

From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:25 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I’ve never seen that done on DB2 for z/OS where you have multiple statements in a single execution. On Db2 for LUW you can create a compound statement, but even for that you can’t open a cursor on an insert. Why not just code without the dynamic SQL? One statement at a time.
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Chairman
IBM Certified Database Adminstrator – DB2 11 DBA for z/OS
IBM Certified System Administrator – DB2 11 for z/OS
IBM Certified Application Developer – DB2 11 for z/OS
IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:02 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I am not getting any sql codes:

[cid:[login to unmask email]

I have the drop cause DB2 will not auto drop the table after you execute the proc.

So I created the continue handler to ignore when dropping an object that does not exists and to ignore creating an object that is already there.

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 9:54 AM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
Robert,

I’m _not_ an expert on Native SQL Stored Procedures (or any other kind).

That said, you have a DROP, a CREATE, and an INSERT on SESSION.tbl1 concatenated into a single execution – are you getting three distinct SQLCODEs on that? If so, are they all SQLCODE=0?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:41 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 z/os NSQL proc Dynamic global table insert


Hello,

I am trying to get a very simple dynamic native stored procedure to work on db2 for z/OS and i can't seem to figure it out why it wont work. I dont get an error message, but it never inserts rows into my table. I have granted publi crole to insert into this table. Does anyone have any ideas? Seems to be fine I dont use the global temp table, but I need to be able to use that. Thanks for your help! All my reading of documentation and forums does not talk about global temp table inside dynamic sql so I am lost to why its not working.

Here is the DDL:

CREATE PROCEDURE Schema1.NativeDynamicProc (
OUT "@O_SQLCODE" INTEGER,
out @O_SqlState varchar(500),
OUT "@O_MSGTEXT" VARCHAR(500) FOR SBCS DATA CCSID EBCDIC,
OUT "@O_ERRORLINENUMBER" INTEGER
)
VERSION V1
LANGUAGE SQL
Result SETS 1
dynamicrules bind
CALLED ON NULL INPUT
MODIFIES SQL DATA
with keep dynamic
P1:
BEGIN

Declare @DSTRING varchar(500);

DECLARE C2 CURSOR FOR S1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DSTRING = 'drop table SESSION.tbl1; '
||'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;'
|| 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from Schema1.TestDelTable where Code < 3; ';

PREPARE S1 FROM @DSTRING;
open C2;
EXECUTE S1;
commit;
--close C2;
END P1#

















-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Robert Krall

Db2 z/os NSQL proc Dynamic global table insert
(in response to Philip Sevetson)
Wow that worked!! I was getting confused why I needed cursors. Removed them and bam it worked!

Thanks so much Phil. I was thinking it all had to be in the same string with 1 execute statement.


Declare @DropSTRING varchar(500);
Declare @CreateDSTRING varchar(500);
Declare @InsertDSTRING varchar(500);
declare @Insert2Statment varchar(500);
declare @DeleteStatement varchar(500);


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DropSTRING = 'drop table SESSION.tbl1; ';
set @CreateDSTRING= 'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;';
set @InsertDSTRING = 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from schema.TestDelTable where Code < 3; ';
set @Insert2Statment = 'Insert into schema.TestDelTable (cola, coldate)'
|| 'Select code, coldate from session.tbl1 ';
Set @DeleteStatement = 'Delete from schema.TestDelTable where Code = 2';

execute immediate @DropSTRING;
execute immediate @CreateDSTRING;
execute immediate @InsertDSTRING;
execute immediate @Insert2Statment;
execute immediate @DeleteStatement;

commit;

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 12:01 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
I don’t understand your conclusion. Why not execute the three statements distinctly within the one stored procedure?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 12:54 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

Sure. I might be out of luck then.

I am trying to delete rows from several tables and instead of creating 30+ procs I wanted to do it with dynamic sql and use one table.

Thank you,
Robert Krall
x8725

From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:25 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I’ve never seen that done on DB2 for z/OS where you have multiple statements in a single execution. On Db2 for LUW you can create a compound statement, but even for that you can’t open a cursor on an insert. Why not just code without the dynamic SQL? One statement at a time.
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Chairman
IBM Certified Database Adminstrator – DB2 11 DBA for z/OS
IBM Certified System Administrator – DB2 11 for z/OS
IBM Certified Application Developer – DB2 11 for z/OS
IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:02 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I am not getting any sql codes:

[cid:[login to unmask email]

I have the drop cause DB2 will not auto drop the table after you execute the proc.

So I created the continue handler to ignore when dropping an object that does not exists and to ignore creating an object that is already there.

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 9:54 AM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
Robert,

I’m _not_ an expert on Native SQL Stored Procedures (or any other kind).

That said, you have a DROP, a CREATE, and an INSERT on SESSION.tbl1 concatenated into a single execution – are you getting three distinct SQLCODEs on that? If so, are they all SQLCODE=0?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:41 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 z/os NSQL proc Dynamic global table insert


Hello,

I am trying to get a very simple dynamic native stored procedure to work on db2 for z/OS and i can't seem to figure it out why it wont work. I dont get an error message, but it never inserts rows into my table. I have granted publi crole to insert into this table. Does anyone have any ideas? Seems to be fine I dont use the global temp table, but I need to be able to use that. Thanks for your help! All my reading of documentation and forums does not talk about global temp table inside dynamic sql so I am lost to why its not working.

Here is the DDL:

CREATE PROCEDURE Schema1.NativeDynamicProc (
OUT "@O_SQLCODE" INTEGER,
out @O_SqlState varchar(500),
OUT "@O_MSGTEXT" VARCHAR(500) FOR SBCS DATA CCSID EBCDIC,
OUT "@O_ERRORLINENUMBER" INTEGER
)
VERSION V1
LANGUAGE SQL
Result SETS 1
dynamicrules bind
CALLED ON NULL INPUT
MODIFIES SQL DATA
with keep dynamic
P1:
BEGIN

Declare @DSTRING varchar(500);

DECLARE C2 CURSOR FOR S1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DSTRING = 'drop table SESSION.tbl1; '
||'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;'
|| 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from Schema1.TestDelTable where Code < 3; ';

PREPARE S1 FROM @DSTRING;
open C2;
EXECUTE S1;
commit;
--close C2;
END P1#

















-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
Attachments

  • image001.png (3.3k)

Philip Sevetson

Db2 z/os NSQL proc Dynamic global table insert
(in response to Robert Krall)
**please note my email address change**
Was “The Blind Leading the Lame” an Old Testament reference? I’m glad it helped. Something for us to remember for future.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 1:44 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

Wow that worked!! I was getting confused why I needed cursors. Removed them and bam it worked!

Thanks so much Phil. I was thinking it all had to be in the same string with 1 execute statement.


Declare @DropSTRING varchar(500);
Declare @CreateDSTRING varchar(500);
Declare @InsertDSTRING varchar(500);
declare @Insert2Statment varchar(500);
declare @DeleteStatement varchar(500);


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DropSTRING = 'drop table SESSION.tbl1; ';
set @CreateDSTRING= 'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;';
set @InsertDSTRING = 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from schema.TestDelTable where Code < 3; ';
set @Insert2Statment = 'Insert into schema.TestDelTable (cola, coldate)'
|| 'Select code, coldate from session.tbl1 ';
Set @DeleteStatement = 'Delete from schema.TestDelTable where Code = 2';

execute immediate @DropSTRING;
execute immediate @CreateDSTRING;
execute immediate @InsertDSTRING;
execute immediate @Insert2Statment;
execute immediate @DeleteStatement;

commit;

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 12:01 PM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
I don’t understand your conclusion. Why not execute the three statements distinctly within the one stored procedure?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 12:54 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

Sure. I might be out of luck then.

I am trying to delete rows from several tables and instead of creating 30+ procs I wanted to do it with dynamic sql and use one table.

Thank you,
Robert Krall
x8725

From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:25 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I’ve never seen that done on DB2 for z/OS where you have multiple statements in a single execution. On Db2 for LUW you can create a compound statement, but even for that you can’t open a cursor on an insert. Why not just code without the dynamic SQL? One statement at a time.
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Chairman
IBM Certified Database Adminstrator – DB2 11 DBA for z/OS
IBM Certified System Administrator – DB2 11 for z/OS
IBM Certified Application Developer – DB2 11 for z/OS
IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:02 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I am not getting any sql codes:

[cid:[login to unmask email]

I have the drop cause DB2 will not auto drop the table after you execute the proc.

So I created the continue handler to ignore when dropping an object that does not exists and to ignore creating an object that is already there.

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 9:54 AM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
Robert,

I’m _not_ an expert on Native SQL Stored Procedures (or any other kind).

That said, you have a DROP, a CREATE, and an INSERT on SESSION.tbl1 concatenated into a single execution – are you getting three distinct SQLCODEs on that? If so, are they all SQLCODE=0?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:41 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 z/os NSQL proc Dynamic global table insert


Hello,

I am trying to get a very simple dynamic native stored procedure to work on db2 for z/OS and i can't seem to figure it out why it wont work. I dont get an error message, but it never inserts rows into my table. I have granted publi crole to insert into this table. Does anyone have any ideas? Seems to be fine I dont use the global temp table, but I need to be able to use that. Thanks for your help! All my reading of documentation and forums does not talk about global temp table inside dynamic sql so I am lost to why its not working.

Here is the DDL:

CREATE PROCEDURE Schema1.NativeDynamicProc (
OUT "@O_SQLCODE" INTEGER,
out @O_SqlState varchar(500),
OUT "@O_MSGTEXT" VARCHAR(500) FOR SBCS DATA CCSID EBCDIC,
OUT "@O_ERRORLINENUMBER" INTEGER
)
VERSION V1
LANGUAGE SQL
Result SETS 1
dynamicrules bind
CALLED ON NULL INPUT
MODIFIES SQL DATA
with keep dynamic
P1:
BEGIN

Declare @DSTRING varchar(500);

DECLARE C2 CURSOR FOR S1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DSTRING = 'drop table SESSION.tbl1; '
||'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;'
|| 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from Schema1.TestDelTable where Code < 3; ';

PREPARE S1 FROM @DSTRING;
open C2;
EXECUTE S1;
commit;
--close C2;
END P1#

















-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Robert Krall

Db2 z/os NSQL proc Dynamic global table insert
(in response to Philip Sevetson)
Yeah! You looked at the issue at a different angel then I was. I now need to get the parameter marker working. It will be good to pass values via ? mark inside the dynamic sql. But This is good sign so far.

Thanks again!

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 12:49 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
Was “The Blind Leading the Lame” an Old Testament reference? I’m glad it helped. Something for us to remember for future.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 1:44 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

Wow that worked!! I was getting confused why I needed cursors. Removed them and bam it worked!

Thanks so much Phil. I was thinking it all had to be in the same string with 1 execute statement.


Declare @DropSTRING varchar(500);
Declare @CreateDSTRING varchar(500);
Declare @InsertDSTRING varchar(500);
declare @Insert2Statment varchar(500);
declare @DeleteStatement varchar(500);


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DropSTRING = 'drop table SESSION.tbl1; ';
set @CreateDSTRING= 'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;';
set @InsertDSTRING = 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from schema.TestDelTable where Code < 3; ';
set @Insert2Statment = 'Insert into schema.TestDelTable (cola, coldate)'
|| 'Select code, coldate from session.tbl1 ';
Set @DeleteStatement = 'Delete from schema.TestDelTable where Code = 2';

execute immediate @DropSTRING;
execute immediate @CreateDSTRING;
execute immediate @InsertDSTRING;
execute immediate @Insert2Statment;
execute immediate @DeleteStatement;

commit;

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 12:01 PM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
I don’t understand your conclusion. Why not execute the three statements distinctly within the one stored procedure?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 12:54 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

Sure. I might be out of luck then.

I am trying to delete rows from several tables and instead of creating 30+ procs I wanted to do it with dynamic sql and use one table.

Thank you,
Robert Krall
x8725

From: Daniel L Luksetich [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:25 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I’ve never seen that done on DB2 for z/OS where you have multiple statements in a single execution. On Db2 for LUW you can create a compound statement, but even for that you can’t open a cursor on an insert. Why not just code without the dynamic SQL? One statement at a time.
Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Chairman
IBM Certified Database Adminstrator – DB2 11 DBA for z/OS
IBM Certified System Administrator – DB2 11 for z/OS
IBM Certified Application Developer – DB2 11 for z/OS
IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:02 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

I am not getting any sql codes:

[cid:[login to unmask email]

I have the drop cause DB2 will not auto drop the table after you execute the proc.

So I created the continue handler to ignore when dropping an object that does not exists and to ignore creating an object that is already there.

Thank you,
Robert Krall
x8725

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 9:54 AM
To: '[login to unmask email]' <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Db2 z/os NSQL proc Dynamic global table insert

**please note my email address change**
Robert,

I’m _not_ an expert on Native SQL Stored Procedures (or any other kind).

That said, you have a DROP, a CREATE, and an INSERT on SESSION.tbl1 concatenated into a single execution – are you getting three distinct SQLCODEs on that? If so, are they all SQLCODE=0?

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Robert Krall [mailto:[login to unmask email]
Sent: Wednesday, April 12, 2017 10:41 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Db2 z/os NSQL proc Dynamic global table insert


Hello,

I am trying to get a very simple dynamic native stored procedure to work on db2 for z/OS and i can't seem to figure it out why it wont work. I dont get an error message, but it never inserts rows into my table. I have granted publi crole to insert into this table. Does anyone have any ideas? Seems to be fine I dont use the global temp table, but I need to be able to use that. Thanks for your help! All my reading of documentation and forums does not talk about global temp table inside dynamic sql so I am lost to why its not working.

Here is the DDL:

CREATE PROCEDURE Schema1.NativeDynamicProc (
OUT "@O_SQLCODE" INTEGER,
out @O_SqlState varchar(500),
OUT "@O_MSGTEXT" VARCHAR(500) FOR SBCS DATA CCSID EBCDIC,
OUT "@O_ERRORLINENUMBER" INTEGER
)
VERSION V1
LANGUAGE SQL
Result SETS 1
dynamicrules bind
CALLED ON NULL INPUT
MODIFIES SQL DATA
with keep dynamic
P1:
BEGIN

Declare @DSTRING varchar(500);

DECLARE C2 CURSOR FOR S1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
@O_SqlCode = DB2_RETURNED_SQLCODE,
@O_SqlState = Returned_SQLState,
@O_MsgText = MESSAGE_TEXT,
@O_ErrorLineNumber = DB2_LINE_NUMBER;
RESIGNAL;
END;

set @DSTRING = 'drop table SESSION.tbl1; '
||'DECLARE GLOBAL TEMPORARY TABLE tbl1 '
||'(CODE INTEGER,COLDATE DATE)ON COMMIT PRESERVE ROWS not logged;'
|| 'Insert into session.tbl1 (CODE,COLDATE)'
|| ' Select CODE,COLDATE from Schema1.TestDelTable where Code < 3; ';

PREPARE S1 FROM @DSTRING;
open C2;
EXECUTE S1;
commit;
--close C2;
END P1#

















-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
Attachments

  • image001.png (3.3k)