host variables in cobol program

[misha]

Hello.

I was wandering if someone could explain to me (or point to some
manual) the process of mapping the addresses of host variables by DB2.
Especially I would like to know when DB2 decides to reinitialize the
addresses and even more when it decides not to do it.

Recently I've ben strucked with a problem of host variables defined in
LINKAGE SECTION, and it
took me some time to find the cause and solution for the problem.
Problem's fixed but i still would like to know more about the whole
process.

regards
Michał Osada

[Frank Swarbrick]

>

>

>>>misha<mi*********@gmail.com11/16/06 9:07 AM >>>

Hello.

I was wandering if someone could explain to me (or point to some
manual) the process of mapping the addresses of host variables by DB2.
Especially I would like to know when DB2 decides to reinitialize the
addresses and even more when it decides not to do it.

Recently I've ben strucked with a problem of host variables defined in
LINKAGE SECTION, and it
took me some time to find the cause and solution for the problem.
Problem's fixed but i still would like to know more about the whole
process.

Can you explain in more detail what your issue is? What do you mean by
"reinitialize the address". All that DB2 does, as far as I can tell, is a
call to a DB2 routine passing the length and type of the host var, along
with (the address of) the host var itself. Take the following, for example,
which is part of a COBOL program after being pre-processed by DB2:

*exec sql fetch ft_select
* into :film-transactions
* end-exec
CALL "sqlgstrt" USING
BY CONTENT SQLA-PROGRAM-ID
BY VALUE 0
BY REFERENCE SQLCA

MOVE 3 TO SQL-STMT-ID
MOVE 7 TO SQLDSIZE
MOVE 3 TO SQLDA-ID

CALL "sqlgaloc" USING
BY VALUE SQLDA-ID
SQLDSIZE
SQL-STMT-ID
0

MOVE 3 TO SQL-HOST-VAR-LENGTH
MOVE 484 TO SQL-DATA-TYPE
MOVE 0 TO SQLVAR-INDEX
MOVE 3 TO SQLDA-ID

CALL "sqlgstlv" USING
BY VALUE SQLDA-ID
SQLVAR-INDEX
SQL-DATA-TYPE
SQL-HOST-VAR-LENGTH
BY REFERENCE FT-BRCH-NBR
OF
FILM-TRANSACTIONS
BY VALUE 0
0

etc...

As long as FT-BRCH-NBR OF FILM-TRANSACTIONS is an addressable item (defined
in working-storage or, if in the linkage section, properly addressed prior
to the DB2 call) there should be no problem. The "sqlgstlv" routine should
simply move the result of the proper column in to the three-byte field
FT-BRCH-NBR passed by the calling program.

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

[Phil Sherman]

You should start by running your source code through the precompiler and
examining the generated Cobol code. (This means that the precompiler
must be a separate job step.) I don't have any samples handy but, if I
recall correctly, the parameters are passed by calling a piece of DB2
code and using the variables as parameters in the call.

Phil Sherman
misha wrote:

Hello.

I was wandering if someone could explain to me (or point to some
manual) the process of mapping the addresses of host variables by DB2.
Especially I would like to know when DB2 decides to reinitialize the
addresses and even more when it decides not to do it.

Recently I've ben strucked with a problem of host variables defined in
LINKAGE SECTION, and it
took me some time to find the cause and solution for the problem.
Problem's fixed but i still would like to know more about the whole
process.

regards
Michał Osada

[Bill]

"Frank Swarbrick" <Fr*************@efirstbank.comwrote in message
news:4s************@mid.individual.net...

>>>>misha<mi*********@gmail.com11/16/06 9:07 AM >>>

Hello.

I was wandering if someone could explain to me (or point to some
manual) the process of mapping the addresses of host variables by DB2.
Especially I would like to know when DB2 decides to reinitialize the
addresses and even more when it decides not to do it.

Recently I've ben strucked with a problem of host variables defined in
LINKAGE SECTION, and it
took me some time to find the cause and solution for the problem.
Problem's fixed but i still would like to know more about the whole
process.

Can you explain in more detail what your issue is? What do you mean by
"reinitialize the address". All that DB2 does, as far as I can tell, is a
call to a DB2 routine passing the length and type of the host var, along
with (the address of) the host var itself. Take the following, for
example,
which is part of a COBOL program after being pre-processed by DB2:

*exec sql fetch ft_select
* into :film-transactions
* end-exec
CALL "sqlgstrt" USING
BY CONTENT SQLA-PROGRAM-ID
BY VALUE 0
BY REFERENCE SQLCA

MOVE 3 TO SQL-STMT-ID
MOVE 7 TO SQLDSIZE
MOVE 3 TO SQLDA-ID

CALL "sqlgaloc" USING
BY VALUE SQLDA-ID
SQLDSIZE
SQL-STMT-ID
0

MOVE 3 TO SQL-HOST-VAR-LENGTH
MOVE 484 TO SQL-DATA-TYPE
MOVE 0 TO SQLVAR-INDEX
MOVE 3 TO SQLDA-ID

CALL "sqlgstlv" USING
BY VALUE SQLDA-ID
SQLVAR-INDEX
SQL-DATA-TYPE
SQL-HOST-VAR-LENGTH
BY REFERENCE FT-BRCH-NBR
OF
FILM-TRANSACTIONS
BY VALUE 0
0

etc...

As long as FT-BRCH-NBR OF FILM-TRANSACTIONS is an addressable item
(defined
in working-storage or, if in the linkage section, properly addressed prior
to the DB2 call) there should be no problem. The "sqlgstlv" routine
should
simply move the result of the proper column in to the three-byte field
FT-BRCH-NBR passed by the calling program.

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

Hi All,
I've always assumed the DB2 Host variables must be in 'working storage'

If you try using host variables contained within a FD record
description - you never get the intended result - so I presume DB2 isn't
aware of any record reads causing the address of the fields within the FD to
change. I presume passing addresses via linkage has the same limitations. So
I alway move the items from linkage to WS and use them from there.

Bill

[misha]

Bill wrote:
(...)

Hi All,
I've always assumed the DB2 Host variables must be in 'working storage'

If you try using host variables contained within a FD record
description - you never get the intended result - so I presume DB2 isn't
aware of any record reads causing the address of the fields within the FD to
change. I presume passing addresses via linkage has the same limitations. So
I alway move the items from linkage to WS and use them from there.

(...)

The problem with using host variables defined in LINKAGE SECTION is
that it usually works ;)
Error appears when you call your program containing sql queries from
two different programs.

Example:
PROG1 calls PROGX and PROGY.
PROGX and PROGY call PROGSQL which uses LINKAGE SECTION variables as
host variables.

Then "strange" things could happen. I was getting SQLCODE +100 on the
first query in the program. And I am sure that the record I was
reffering to was present in the table. After some experimenting it
turned out that the DB2 refers to a different place in memory than
COBOL variables. It looks like during the first call of PROGSQL DB2
remembers the addresses of host variables. During the second call the
addresses are not "reinitialized" and DB2 refers to the wrong place in
memory.
If the variables from WORKING STORAGE are used as host variables
everything is OK.

My guess is that because WORKING STORAGE is allocated separately for
each of the
calls of the PROGSQL program - DB2 reinitializes the addresses every
time the program is called. When the variables from LINKAGE SECTION are
used as host variables DB2 decides that it is not necessery, to
initialize the adresses again.

If the PROGSQL would be called only from PROGX using LINKAGE SECTION
variables as host variables it would propably cause no harm (although
I'm not completly sure of it).

Anyway - to solve the problem I decided to copy the contents of LINKAGE
SECTION variables to some WORKING STORAGE variables and use them in
queries. It helped.
I learned that I could also set the SQL-INIT-FLAG at the beginning of
the program to tell
DB2 to reinitialize the addresses - but I didn't try it.

I was wondering if someone have similar problems and maybe know some
more about how DB2 decides to initialize or not to initialize the host
variables addresses.

regards
Michal Osada

[Frank Swarbrick]

Bill<wf**@optonline.net11/19/06 12:50 PM >>>

>"Frank Swarbrick" <Fr*************@efirstbank.comwrote in message
news:4s************@mid.individual.net...

>

>misha<mi*********@gmail.com11/16/06 9:07 AM >>>
Hello.

I was wandering if someone could explain to me (or point to some
manual) the process of mapping the addresses of host variables by DB2.
Especially I would like to know when DB2 decides to reinitialize the
addresses and even more when it decides not to do it.

Recently I've ben strucked with a problem of host variables defined in
LINKAGE SECTION, and it
took me some time to find the cause and solution for the problem.
Problem's fixed but i still would like to know more about the whole
process.

Can you explain in more detail what your issue is? What do you mean by
"reinitialize the address". All that DB2 does, as far as I can tell, is

a

>call to a DB2 routine passing the length and type of the host var, along
with (the address of) the host var itself. Take the following, for
example,
which is part of a COBOL program after being pre-processed by DB2:

[deleted]

>>
As long as FT-BRCH-NBR OF FILM-TRANSACTIONS is an addressable item
(defined
in working-storage or, if in the linkage section, properly addressed

prior

>to the DB2 call) there should be no problem. The "sqlgstlv" routine
should
simply move the result of the proper column in to the three-byte field
FT-BRCH-NBR passed by the calling program.

Hi All,
I've always assumed the DB2 Host variables must be in 'working

storage'

>
If you try using host variables contained within a FD record
description - you never get the intended result - so I presume DB2 isn't
aware of any record reads causing the address of the fields within the FD

to

>change. I presume passing addresses via linkage has the same limitations.

So

>I alway move the items from linkage to WS and use them from there.

I won't comment on using host variables in the file section, as there may be
other issued, but I see no reason why they could not exist in the linkage
section.
For example, I would think that something like this should work:

LINKAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 linkage1 PIC X(10).
01 linkage2 PIC S9(5) COMP-3.
EXEC SQL END DECLARE SECTION END-EXEC
PROCEDURE DIVISION USING linkage1, linkage2.
EXEC SQL SELECT DATA1, DATA2
INTO :linkage2
FROM TABLE_NAME
WHERE DATA1 = :linkage1

In fact I have used something like this (a bit more realistic, of course),
and had no problems.

Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

[Frank Swarbrick]

misha<mi*********@gmail.com11/20/06 1:23 AM >>>

>
The problem with using host variables defined in LINKAGE SECTION is
that it usually works ;)
Error appears when you call your program containing sql queries from
two different programs.

Example:
PROG1 calls PROGX and PROGY.
PROGX and PROGY call PROGSQL which uses LINKAGE SECTION variables as
host variables.

Then "strange" things could happen. I was getting SQLCODE +100 on the
first query in the program. And I am sure that the record I was
reffering to was present in the table. After some experimenting it
turned out that the DB2 refers to a different place in memory than
COBOL variables. It looks like during the first call of PROGSQL DB2
remembers the addresses of host variables. During the second call the
addresses are not "reinitialized" and DB2 refers to the wrong place in
memory.
If the variables from WORKING STORAGE are used as host variables
everything is OK.

My guess is that because WORKING STORAGE is allocated separately for
each of the
calls of the PROGSQL program - DB2 reinitializes the addresses every
time the program is called. When the variables from LINKAGE SECTION are
used as host variables DB2 decides that it is not necessery, to
initialize the adresses again.

If the PROGSQL would be called only from PROGX using LINKAGE SECTION
variables as host variables it would propably cause no harm (although
I'm not completly sure of it).

Anyway - to solve the problem I decided to copy the contents of LINKAGE
SECTION variables to some WORKING STORAGE variables and use them in
queries. It helped.
I learned that I could also set the SQL-INIT-FLAG at the beginning of
the program to tell
DB2 to reinitialize the addresses - but I didn't try it.

I was wondering if someone have similar problems and maybe know some
more about how DB2 decides to initialize or not to initialize the host
variables addresses.

Are you using DB2 UDB for z/OS? My guess is that the pre-processor there is
somewhat different than the LUW pre-processor. Sounds like, as you say,
it's taking the address of the variable from the first call to PROGSQL and
using that as the address of the host variable even when PROGSQL is called
from another program (or even the same program if calling using different
data areas).

Anyway, since the SQL-INIT-FLAG is documented, why don't you use that?

From what I can see this should not happen for LUW, but I could be wrong. I
will certainly keep it in mind when developing my own subroutines! :-)

Frank
---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

[misha]

Hi all.

After digging through some manuals I finally found the answer (at least
I hope I did ;)

First I must add that the program I was having problem with is a batch
program.

Basically the problem with wrong host variables is caused by using
LINKAGE SECTION variables.

When program is called for the first time in a run-unit, it is loaded
and the memory for it's WORKING STORAGE is allocated. Until the
run-unit ends - address of WORKING
STORAGE are not going to change. When the PROG1 calls PROGX for the
first time, the
program is loaded and the memory for WORKING STORAGE is allocated. Then
the PROGX
calls PROGSQL passing it's own WORKING STORAGE variables to PROGSQL's
LINKAGE
SECTION. Because it is a first invokation of PROGSQL - the program is
loaded and the
memory for WORKING STORAGE is allocated. When the program PROGSQL
starts, it performs the section inputed by the DB2 precompiler:

PROCEDURE DIVISION.

DSNSQL SECTION.
SQL-SKIP.
GO TO SQL-INIT-END.
SQL-INITIAL.
MOVE 1 TO SQL-INIT-FLAG.
CALL 'DSNHADDR' USING SQL-VPARMPTR OF SQL-PLIST15 SQL-PVAR-LIST15.

(...)

The address of host variables are stored and the flag SQL-INIT-FLAG is
set to 1.

When the program PROGSQL is called from PROGY, the PROGSQL is already
loaded and it's WORKING STORAGE memory allocated. The flag
SQL-INIT-FLAG has been already set and the section, in which the
addresses are "remembered", is skipped. Unfortunatelly the PROGSQL was
invoked for the second time with the LINKAGE SECTION containing the
variables from PROGY's WORKING STORAGE. And, naturally, PROGY's WORKING

STORAGE variables addresses differ from the PROGX's WORKING STORAGE
variables
addresses.

Conclusion:
- Do not use LINKAGE SECTION variables as host variables. Move them to
WORKING STORAGE variables which are safe to use as host variables.
- If you have to use LINKAGE SECTION variables as host variables you
must set the
value of SQL-INIT-FLAG to '0' at the beginning of the program.

I hope I expressed my thoughts in a clear way.
Thanks for help!

best regards
Michal Osada