MVSFORUMS.com

dragone_007 · Beginner Joined: 18 Mar 2008 Posts: 24 Topics: 6

Hello,

I'm getting an Abend while trying to perform a FETCH statement in a var-list Dynamic Select operation. I tryed the following:

jsharon1248 · Posted: Thu Mar 05, 2009 9:48 am Post subject:

Varying list dynamic SQL is extremely difficult. I hope you've done your homework and you really need to do this.

The DECLARE is a not an executable statement, but it must precede all statements that explicitely refer to the cursor by name. You reference the cursor name in your PREPARE, but coded the PREPARE and the DESCRIBE before the DECLARE. Most shops want the DECLAREs in WS. Some put them in the Procedure Division. I think it makes more sense to put them in WS because they are not executable. Just make sure that the DECLARE is physically before the PREPARE. You should also issue the PREPARE with the INTO clause. That will eliminate the need for the DESCRIBE. PREPARE/INTO is equivalent to PREPARE followed by DECSRIBE. Either is fine, but doing it in one statement seems to make more sense to me. After you issue the PREPARE/INTO, that's where the fun starts. You need to dig through the SQLDA, and allocate storage for each column specified in the SELECT list. This is very complicated because you need to be able to reference each area of storage with the correct COBOL variable type. I don't like the IBM example for allocating storage at all. I defined different COBOL variables in the LS, and then used the CEEGTST LE service to allocate the storage for them. A little tricky, but I think it's a better solution than the sample. Your OPEN is incomplete because you defined the cursor with parameter markers, but did not code the USING clause. You need to populate variables with the values you want used by the OPEN, and then code the OPEN with the USING clause referencing those variables.

I'll stress this again. This should only be done if there are really no other options. This is very powerful, but it takes much longer to develop the programs especially if it's your first time.

dragone_007 · Beginner Joined: 18 Mar 2008 Posts: 24 Topics: 6

Thaks a lot for your time jsharon1248,

I followed your advice to DECLARE first and to PREPARE and DESCRIBE after and this gave no error in compilation but, I'm still having the same abend:
CEE3250C The system or user abend S04E R=00E7000C was issued. From compile unit AGPEK030 at entry point AGPEK030 at statement 2936 at compile unit offset +0000589C at entry offset +0000589C at address 1BA8F46C.

I also digged into SQLDA area and found all information regarding the Column NAMES and TYPE's of each like this:

SQLDAID = SQLDA
SQLDABC = 0000001776
CAMPI IN OUTPUT (SQLN) = 00040
CAMPI IN INPUT (SQLD) = 00040
SQLTYPE = 00452
SQLLEN = 00012
SQLNAMEL = 00015
SQLNAMEC = NUMERO_RAPPORTO
SQLDATA = 0000001144
SQLIND = 0000000000
SQLLONGLEN = 0029622284
SQLDATALEN = 0000000000
SQLDATATYPE-NAMEL = 00015
SQLDATATYPE-NAMEC = NUMERO_RAPPORTO

and so on for my 40 COLUMNS

but how can I pass (and where) the size of each column again to the SQLDA area ? What is the CEEGTST LE service that you mentioned ? How can I access to that ? Thanks a lot again for your effort

jsharon1248 · Posted: Thu Mar 05, 2009 12:42 pm Post subject:

Here is a link that will direct you to a manual for LE. Scroll down to Language Environment, and open the LE Programming Reference Manual. Chapter 2 provides details about all the Callable Services, including CEEGTST (section 2.2.5.45).
http://publibz.boulder.ibm.com/bookmgr_OS390/libraryserver/zosv1r9/

The DECLARE, PREPARE/INTO, and OPEN/USING are straightforward. The FETCH is the statement where things get tricky. I believe that you're having problems because you're issuing your FETCH before you acquire storage for the columns in the SELECT list. After the PREPARE/INTO but before the first FETCH, you need to allocate storage to store the values returned by the FETCH for each column in the SELECT list. You need to store the address for each host variable in the SQLDA. COBOL is not at all accomodating with this type of work. What I do, is define generic variables in the Linkage Section, and then SET POINTERs to access the data returned. To start, here's how I define the most common generic variables in LS.

dragone_007 · Beginner Joined: 18 Mar 2008 Posts: 24 Topics: 6

Hi jsharon1248,

Thanks a lot for your time and example provided. I tryed to apply the CALL to the CEEGTST program to allocate the storage I need by gives me an error in compilation saying that the COPY CEEIGZCT has not been found and so the RC=12. By the way I did an implementation following the example shawn in "Squeezing the Most Out of Dynamic SQL with DB2 for z/OS and OS/390" (manual) to what I wrote yesterday like this:

jsharon1248 · Posted: Fri Mar 06, 2009 2:42 pm Post subject:

The CEEIGZCT copybook is in an installation specific copylib. You'd have to find out the copylib DSN at your site to use it. I hard coded my own version.

dragone_007 · Beginner Joined: 18 Mar 2008 Posts: 24 Topics: 6

Hi jsharon1248,

If you want I can send you the latest manual's versions regarding Dynamic SQL. Unfortunately I don't have any link to post. By the way are IBM Redbook series about DB2 version 8. Tell me where do I have to send those to you. Thanks again by the way.

dragone_007 · Beginner Joined: 18 Mar 2008 Posts: 24 Topics: 6

Sorry jsharon1248,

I forgot to post the results of GET DIAGNOSTICS :

jsharon1248 · Posted: Mon Mar 09, 2009 12:22 pm Post subject:

I haven't use the GET DIAGNOSTICs. But if I'm interpreting it's output correctly, the problem is that you haven't supplied the correct SQLDA length. Have you computed and set the length of the SQLDA before the FETCH? I used the following. You need to make sure SQLN is > 0.

dragone_007 · Beginner Joined: 18 Mar 2008 Posts: 24 Topics: 6

Hi jsharon1248,

I followed your advice in calculating the SQLDA area for the return values of my select so I defined another area in WORKING-STORAGE similar to SQLDA like this:

dragone_007 · Beginner Joined: 18 Mar 2008 Posts: 24 Topics: 6

Sorry I forgot the redefining items of MY-SQLVAR2:

jsharon1248 · Posted: Thu Mar 12, 2009 10:47 am Post subject:

I'm confused as to why you defined a separate SQLDA. Just use the original SQLDA to eliminate a source of confustion. The problem is in your setup of the SQLDA prior to the first fetch. You're showing SQLDATA(x) with the value 12. When you execute the FETCH, DB2 is attempting to store the results of that column at address 000012, which is owned by the operating system. SQLDATA(x) needs to be a valid address accessible by your program. You need to store the address returned by CEEGTST into SQLDATA(x). If you're going to use the IBM sample program mechanism, you'll need to bump the address in the POINTER variable manually.

I recommend that you simplify your SQL to return one column. Get that one column working and then add your loop for the other columns later.