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

Need help - DB2 Dynamic SQL Program embeded in Cobol


IBM Mainframe Forums -> DB2
Post new topic   This topic is locked: you cannot edit posts or make replies.
View previous topic :: View next topic  
Author Message
NikhilGuptaGaya

New User


Joined: 28 May 2018
Posts: 5
Location: India

PostPosted: Sat Jun 23, 2018 1:58 pm
Reply with quote

Hi All,

I will need your help in clearing my doubts that haunted me for each time I start studying DB2.

Got following queries -

1. Regarding Prepare statement, let's take below example -

MOVE "DELETE FROM SAMPLETABLE WHERE DEPTNO = 'A00'"
TO STRING-VARIABLE.

EXEC SQL
PREPARE STMT1 FROM :STRING-VARIABLE;
END-EXEC.

EXEC SQL
EXECUTE STMT1;
END-EXEC.

I didn't get where "STMT1" is declared/defined in Working Storage of cobol program. Does it require to be defined in the working storage or not? if yes then how and what should be it's picture clause and length.

2) It's regarding Dynamic SQL....

Move "SQL query" to WorkingStorge-HostVariable.

Above "SQL Query" can be given at runtime using SYSIN DD control statement of JCL?

3) If "YES" that sql query can be given to the program using SYSIN DD DATA statement of JCL then how SQLDA becomes useful?

also can you give me one complete example of it having step by step without any confusion or missing declaring/definition in the working storage?

4) It's regarding varying list select query....let's take below example -

SQL to execute: SELECT PROJNO, PROJNAME, RESPEMP
FROM DSN8810.PROJ
WHERE PROJNO = 'A00'
AND PRSTDATE = '1988-10-10';

Move the "SQL to execute" to STRING-VARIABLE

EXEC SQL DECLARE CSR3 CURSOR FOR VLSQL;

EXEC SQL
PREPARE VLSQL INTO SQLDA FROM :STRING-VARIABLE;
EXEC SQL OPEN CSR3;

Load storage addresses into the SQLDA

Loop until no more rows to FETCH
EXEC SQL FETCH CSR3 USING DESCRIPTOR SQLDA;

EXEC SQL CLOSE CSR3;

In above query, I'm not getting followings -

a. where VLSQL is defined in program? what's its picture clause and length?
b. How SQLDA is used to define & mapped column of select query?
c. fetch cursor using decriptor SQLDA.....how columns are mapped to host variable? is it not required to be declared in the working storage section?

also could you explain below code snippet - it's not clear to me....

EXEC SQL INCLUDE SQLDA
EXEC SQL INCLUDE MINSQLDA

Read "SQL to execute" from external source

Move the "SQL to execute" to STRING-VARIABLE

EXEC SQL DECLARE CSR3 CURSOR FOR VLSQL;

EXEC SQL
PREPARE VLSQL INTO MINSQLDA FROM :STRING-VARIABLE;

IF SQLD IN MINSQLDA = 0
EXECUTE IMMEDIATE (SQL statement was not a SELECT)
FINISHED.

EXEC SQL
PREPARE VLSQL INTO SQLDA FROM :STRING-VARIABLE;
EXEC SQL OPEN CSR3;

Load storage addresses into the SQLDA

Loop until no more rows to FETCH
EXEC SQL FETCH CSR3 USING DESCRIPTOR SQLDA;

EXEC SQL CLOSE CSR3;

Please suggest...

Thank you....
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sun Jun 24, 2018 12:10 am
Reply with quote

Quote:
I didn't get where "STMT1" is declared/defined in Working Storage of cobol program. Does it require to be defined in the working storage or not? if yes then how and what should be it's picture clause and length.
You really need to clear concepts about dynamic sqls , you don't need to declare. also read more here.
PREPARE

Quote:
EXEC SQL DECLARE CSR3 CURSOR FOR VLSQL;

VLSQL its part of Prepare statement, so no declaration.
Back to top
View user's profile Send private message
NikhilGuptaGaya

New User


Joined: 28 May 2018
Posts: 5
Location: India

PostPosted: Sun Jun 24, 2018 12:47 am
Reply with quote

Thank you Rohit for your response and time.

STMT1 ------> I picked it from the below snippet ---

MOVE "DELETE FROM SAMPLETABLE WHERE DEPTNO = 'A00'"
TO STRING-VARIABLE.

EXEC SQL
PREPARE STMT1 FROM :STRING-VARIABLE;
END-EXEC.

Now I am not understanding that where STMT1 is defined in cobol program and where it need to be defined? what's its picture clause & length?

As I understand that prepare "STMT1" from it came? somewhere stmt1 need to be declared as variable in the program as it's statement where best access path is stored and prepared from "host variable containing sql query".

Please suggest.
Back to top
View user's profile Send private message
prino

Senior Member


Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania

PostPosted: Sun Jun 24, 2018 1:46 am
Reply with quote

NikhilGuptaGaya wrote:
Please suggest.

This is a forum for experts, and experts are not afraid to RTFM.

DO SO, NOW!

And don't waste our time repeating the same question (that has already been answered) again!
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Sun Jun 24, 2018 1:47 am
Reply with quote

search the IDUG freebies for CSNTEPC
Back to top
View user's profile Send private message
NikhilGuptaGaya

New User


Joined: 28 May 2018
Posts: 5
Location: India

PostPosted: Sun Jun 24, 2018 3:05 pm
Reply with quote

Thank you Prino and also I apologies for my knowledge gap.

Thank you Enrico. I didn't get CSNTEPC meant for? I got IDUG community site over google and started going through it as well.

For now, I understood that "statement-name" followed prepare can be any word name that doesn't required to be declared/defined in the whole program. This need to be used directly and DB2 handles it own internally.

Thank you. Further, still above all I want to take your help in learning. please explain me below queries which I got after going through the code mentioned also below after the questions.

a) Why Prepare statement into SQLDA? Why can't I simply write Prepare Statement from :Host-Variable instead Prepare Statement into SQLDA from :Host-Variable?.

b) How "Load storage addresses into the SQLDA" is possible?

c) how parameter marker can be used in varying list select sql query?

d) I understand that for varying list sql query is not known beforehand. The query can be supplied at the run time in the program. For each run, sql query can vary also in the program or user can input any query at runtime using JCL control statement. Am I correct?

e) Why MINSQLDA is used? and also I've noticed at google that MinSQLDA < 50 or > 50 based on which we need to redefine whole prepare statement again into SQLDA descriptor. Why?

EXEC SQL INCLUDE SQLDA
EXEC SQL INCLUDE MINSQLDA

Read "SQL to execute" from external source

Move the "SQL to execute" to STRING-VARIABLE

EXEC SQL DECLARE CSR3 CURSOR FOR VLSQL;

EXEC SQL
PREPARE VLSQL INTO MINSQLDA FROM :STRING-VARIABLE;

IF SQLD IN MINSQLDA = 0
EXECUTE IMMEDIATE (SQL statement was not a SELECT)
FINISHED.

EXEC SQL
PREPARE VLSQL INTO SQLDA FROM :STRING-VARIABLE;
EXEC SQL OPEN CSR3;

Load storage addresses into the SQLDA

Loop until no more rows to FETCH
EXEC SQL FETCH CSR3 USING DESCRIPTOR SQLDA;

EXEC SQL CLOSE CSR3;
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Mon Jun 25, 2018 2:17 pm
Reply with quote

Topic locked. Please ask only ONE question per topic. This is so that people looking for answers in the future do not get confused.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies. View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Using API Gateway from CICS program CICS 0
No new posts COBOL -Linkage Section-Case Sensitive COBOL Programming 1
No new posts COBOL ZOS Web Enablement Toolkit HTTP... COBOL Programming 0
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts Generate random number from range of ... COBOL Programming 3
Search our Forums:

Back to Top