View previous topic :: View next topic
|
Author |
Message |
NikhilGuptaGaya
New User
Joined: 28 May 2018 Posts: 5 Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
NikhilGuptaGaya
New User
Joined: 28 May 2018 Posts: 5 Location: India
|
|
|
|
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 |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1306 Location: Vilnius, Lithuania
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
search the IDUG freebies for CSNTEPC |
|
Back to top |
|
|
NikhilGuptaGaya
New User
Joined: 28 May 2018 Posts: 5 Location: India
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
|