Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need help - DB2 Dynamic SQL Program embeded in Cobol

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Need help - DB2 Dynamic SQL Program embeded in Cobol
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

Senior Member


Joined: 21 Sep 2010
Posts: 1942
Location: NY,USA

PostPosted: Sun Jun 24, 2018 12:10 am    Post subject:
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    Post subject: Reply to: Need help - DB2 Dynamic SQL Program embeded in Cobol
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: 1113
Location: Oostende, Belgium

PostPosted: Sun Jun 24, 2018 1:46 am    Post subject: Re: Reply to: Need help - DB2 Dynamic SQL Program embeded in Cobol
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

Senior Member


Joined: 14 Mar 2007
Posts: 10488
Location: italy

PostPosted: Sun Jun 24, 2018 1:47 am    Post subject: Reply to: Need help - DB2 Dynamic SQL Program embeded in Cobol
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    Post subject: Reply to: Need help - DB2 Dynamic SQL Program embeded in Cobol
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: 2035
Location: UK

PostPosted: Mon Jun 25, 2018 2:17 pm    Post subject: Reply to: Need help - DB2 Dynamic SQL Program embeded in Cobol
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.    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 Error while initiation CICS program f... Ashishpanpaliya CICS 3 Mon Jul 16, 2018 5:12 pm
No new posts SQL IN predicate using COBOL table jerryte DB2 16 Fri Jun 29, 2018 10:30 pm
No new posts Converting SAS code to Cobol or Easyt... Ashish.Raghav All Other Mainframe Topics 6 Fri Jun 29, 2018 6:01 pm
No new posts In which program the FILE CONTROL wil... Poha Eater COBOL Programming 2 Thu Jun 28, 2018 9:02 pm
No new posts Can we use a file both as input and o... Chiku kumar COBOL Programming 11 Thu Jun 28, 2018 3:30 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us