Re: help with tables

On 1/29/2008 at 9:37 PM, in message
<15ovp3p40dmvcovmgc3co257gi595dn3ii@xxxxxxx>, Robert<no@xxxxxx> wrote:
If you don't like BULK COLLECT syntax, leave it out. Selecting into an
array does the
same thing and isn't tied to Oracle. It works on DB2 the same way. For
example:

01 table2-area.
05 table2-entry occurs 1 to 100 depending on sqlerrd (3).
10 t2-name pic x(30).
10 t2-dept pic x(04).

compute sqlerrd(3) = length of table2-area / length of table2-entry
select name, department into table2-entry from table2
where name in (select distinct name from table1)
display 'There are ' sqlerrd(3) ' departments.'

This does not appear to work, at least not using the DB2 pre-processor.

Here's my test program:

IDENTIFICATION DIVISION.
PROGRAM-ID. tabtest.
ENVIRONMENT DIVISION.

DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 table2-area.
05 table2-entry occurs 1 to 100 depending on sqlerrd(3).
10 t2-name pic x(30).
10 t2-dept pic x(04).
EXEC SQL
END DECLARE SECTION
END-EXEC
77 ERRLOC PIC X(80) VALUE SPACES.

PROCEDURE DIVISION.
CALL 'connect'
PERFORM get-into-table.
PERFORM DB-DISCONNECT
STOP RUN.


get-into-table.
compute sqlerrd(3) = length of table2-area
/ length of table2-entry
exec sql
select name, dept
into :table2-entry
from test.table2
where name in (select distinct name from test.table1)
end-exec
CALL 'checkerr' USING SQLCA ERRLOC

display 'There are ' sqlerrd(3) ' departments.'
exit.

DB-DISCONNECT.
MOVE 'DB-DISCONNECT' TO ERRLOC
EXEC SQL
CONNECT RESET
END-EXEC
CALL 'checkerr' USING SQLCA ERRLOC
EXIT.

END PROGRAM tabtest.


And here's the output from the DB2 pre-processor:
Database Connection Information

Database server = DB2/NT 9.5.0
SQL authorization ID = FSWARBRI
Local database alias = TEST1

prep tabtest.sqb BINDFILE TARGET ibmcob CALL_RESOLUTION immediate EXPLAIN
yes EXPLSNAP yes

LINE MESSAGES FOR tabtest.sqb
------
--------------------------------------------------------------------
SQL0060W The "COBOL" precompiler is in progress.
19 SQL0008N The token "occurs" found in a host variable
declaration is not valid.
41 SQL4943W The number of host variables in the INTO clause
is not the same as the number of items in the SELECT
clause. SQLSTATE=01503
41 SQL0306N The host variable "TABLE2-ENTRY" is undefined.
SQL0095N No bind file was created because of previous
errors.
SQL0091W Precompilation or binding was ended with "3"
errors and "1" warnings.

Frank

.

Relevant Pages

  • Re: help with tables
    ... department into table2-entry from table2 ... This does not appear to work, at least not using the DB2 pre-processor. ... EXEC SQL ... BEGIN DECLARE SECTION ...
    (comp.lang.cobol)
  • Re: help with tables
    ... department into table2-entry from table2 ... This does not appear to work, at least not using the DB2 pre-processor. ... EXEC SQL ... BEGIN DECLARE SECTION ...
    (comp.lang.cobol)
  • Re: help with tables
    ... declaration is not valid. ... 51 SQL0306N The host variable "TABLE2-ENTRY" is undefined. ... Here is an sample from the IBM DB2 manual: ... "DB2 Call Level Interface (CLI) versus embedded dynamic SQL ...
    (comp.lang.cobol)
  • Re: PL/I coded DB2 Stored Procedure
    ... As for the info n the DB2 side, I assumed the "DB2 SQL Pre-Compiler Ver ... IN_BBB_NM Char;, this Stored Proc also works fine, but it defeats ... Presumably you have some form of Windows - z/OS connectivity? ... >> exec sql declare zzz table ...
    (comp.lang.pl1)
  • Re: help with tables
    ... declaration is not valid. ... 51 SQL0306N The host variable "TABLE2-ENTRY" is undefined. ... SQL0095N No bind file was created because of previous ... Here is an sample from the IBM DB2 manual: ...
    (comp.lang.cobol)