SQL Eroor 8100 - Need Help

[Adam “Adamlivinlife” Kamal]

Hello Everyone

I am getting SQL error 8100 on a FETCH in a Cobol program. It's sales history table so it has millions of record. The record has a sequence number as a part of the key, SEQ-NUM = 1 indicate the current month record.

On the beginning of each month, after fetching the record, program will do MAX (SEQ-NUM) and reinsert the record with the highest SEQ-NUM, it.

It looks like these insert while FETCHING causes SQL 8100.

My coworker suggested to close the cursor and reopen it after each read/insert (after saving the position of record).

The table has ~1 million records and just did not think open and close the cursor a million times is the best approach. Any idea how to process these kind of history.

Any idea on how to properly process these records other than close and reopen for each record will be highly appreciated.


Cursor:
EXEC SQL
DECLARE GET_SALES_HISTORY CURSOR FOR
SELECT
WARE
, PART_NUM
, REPORT_DATE
,SALES
FROM =HISTORY
WHERE SEQ_NUM = 1
BROWSE ACCESS
END-EXEC.
/

PROCEDURE DIVISION.
EXEC SQL
FETCH GET_HISTORY
INTO :WARE
,:PART-NUM
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
END-EXEC
IF SQLCODE = 0
MOVE REPORT-DATE O TO HV-REPORT-DATE
IF HV-REPORT-DATE-MM NOT = SYS-MM
EXEC SQL
SELECT MAX(SEQ-NUM)
INTO :HV-MAX-SEQ-NUM
FROM =HISTORY
WHERE WARE = :WARE
AND PART_NUM = :PART-NUM
BROWSE ACCESS
END-EXEC
IF SQLCODE = 0
ADD 1 TO HV-MAX-SEQ-NUM
EXEC SQL
INSERT INTO =HISTORY
( WARE
, PART_NUM
, MAX_SEQ_NUM
, REPORT_DATE
, SALES
)
VALUES
( :WARE
,:PART-NUM
,:HV-MAX-SEQ-NUM
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
)
END-EXEC
END-IF
END-IF
END-IF

EXIT-SECTION
EXIT

[Randall]

Post by Adam “Adamlivinlife” Kamal
Hello Everyone
I am getting SQL error 8100 on a FETCH in a Cobol program. It's sales history table so it has millions of record. The record has a sequence number as a part of the key, SEQ-NUM = 1 indicate the current month record.
On the beginning of each month, after fetching the record, program will do MAX (SEQ-NUM) and reinsert the record with the highest SEQ-NUM, it.
It looks like these insert while FETCHING causes SQL 8100.
My coworker suggested to close the cursor and reopen it after each read/insert (after saving the position of record).
The table has ~1 million records and just did not think open and close the cursor a million times is the best approach. Any idea how to process these kind of history.
Any idea on how to properly process these records other than close and reopen for each record will be highly appreciated.
EXEC SQL
DECLARE GET_SALES_HISTORY CURSOR FOR
SELECT
WARE
, PART_NUM
, REPORT_DATE
,SALES
FROM =HISTORY
WHERE SEQ_NUM = 1
BROWSE ACCESS
END-EXEC.
/
PROCEDURE DIVISION.
EXEC SQL
FETCH GET_HISTORY
INTO :WARE
,:PART-NUM
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
END-EXEC
IF SQLCODE = 0
MOVE REPORT-DATE O TO HV-REPORT-DATE
IF HV-REPORT-DATE-MM NOT = SYS-MM
EXEC SQL
SELECT MAX(SEQ-NUM)
INTO :HV-MAX-SEQ-NUM
FROM =HISTORY
WHERE WARE = :WARE
AND PART_NUM = :PART-NUM
BROWSE ACCESS
END-EXEC
IF SQLCODE = 0
ADD 1 TO HV-MAX-SEQ-NUM
EXEC SQL
INSERT INTO =HISTORY
( WARE
, PART_NUM
, MAX_SEQ_NUM
, REPORT_DATE
, SALES
)
VALUES
( :WARE
,:PART-NUM
,:HV-MAX-SEQ-NUM
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
)
END-EXEC
END-IF
END-IF
END-IF
EXIT-SECTION
EXIT

First, you need to worry about TMF or SQL transactions in all this. That's always a consideration.

Second, your INSERT should embed the SELECT so that you can do the insert within a single SQL operation, which will make the operation more efficient and within one transaction. Something like:
BEGIN WORK;
INSERT INTO =HISTORY
( WARE
, PART_NUM
, MAX_SEQ_NUM
, REPORT_DATE
, SALES
)
VALUES
( :WARE
, :PART-NUM
(SELECT MAX(SEQ-NUM)
FROM =HISTORY
WHERE WARE = :WARE
AND PART_NUM = :PART-NUM)
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
)
COMMIT WORK;

or something along those lines. You may need to use multiple correlation ids and multiple host variables. This does work in SQL/MX, but I'm not sure about SQL/MP (it's been a while).

-Randall

[Keith Dick]

Post by Randall

Post by Adam “Adamlivinlife” Kamal
Hello Everyone
I am getting SQL error 8100 on a FETCH in a Cobol program. It's sales history table so it has millions of record. The record has a sequence number as a part of the key, SEQ-NUM = 1 indicate the current month record.
On the beginning of each month, after fetching the record, program will do MAX (SEQ-NUM) and reinsert the record with the highest SEQ-NUM, it.
It looks like these insert while FETCHING causes SQL 8100.
My coworker suggested to close the cursor and reopen it after each read/insert (after saving the position of record).
The table has ~1 million records and just did not think open and close the cursor a million times is the best approach. Any idea how to process these kind of history.
Any idea on how to properly process these records other than close and reopen for each record will be highly appreciated.
EXEC SQL
DECLARE GET_SALES_HISTORY CURSOR FOR
SELECT
WARE
, PART_NUM
, REPORT_DATE
,SALES
FROM =HISTORY
WHERE SEQ_NUM = 1
BROWSE ACCESS
END-EXEC.
/
PROCEDURE DIVISION.
EXEC SQL
FETCH GET_HISTORY
INTO :WARE
,:PART-NUM
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
END-EXEC
IF SQLCODE = 0
MOVE REPORT-DATE O TO HV-REPORT-DATE
IF HV-REPORT-DATE-MM NOT = SYS-MM
EXEC SQL
SELECT MAX(SEQ-NUM)
INTO :HV-MAX-SEQ-NUM
FROM =HISTORY
WHERE WARE = :WARE
AND PART_NUM = :PART-NUM
BROWSE ACCESS
END-EXEC
IF SQLCODE = 0
ADD 1 TO HV-MAX-SEQ-NUM
EXEC SQL
INSERT INTO =HISTORY
( WARE
, PART_NUM
, MAX_SEQ_NUM
, REPORT_DATE
, SALES
)
VALUES
( :WARE
,:PART-NUM
,:HV-MAX-SEQ-NUM
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
)
END-EXEC
END-IF
END-IF
END-IF
EXIT-SECTION
EXIT

First, you need to worry about TMF or SQL transactions in all this. That's always a consideration.
BEGIN WORK;
INSERT INTO =HISTORY
( WARE
, PART_NUM
, MAX_SEQ_NUM
, REPORT_DATE
, SALES
)
VALUES
( :WARE
, :PART-NUM
(SELECT MAX(SEQ-NUM)
FROM =HISTORY
WHERE WARE = :WARE
AND PART_NUM = :PART-NUM)
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
)
COMMIT WORK;
or something along those lines. You may need to use multiple correlation ids and multiple host variables. This does work in SQL/MX, but I'm not sure about SQL/MP (it's been a while).
-Randall

I am assuming SQL/MP.
I am assuming the line FETCH GET_HISTORY should be FETCH GET_SALES_HISTORY, since GET_SALES_HISTORY is the name
given for the cursor.
I am assuming that in the line MOVE REPORT-DATE O TO HV-REPORT-DATE , the O standing by itself is a typo and should not be there.

The error 8100 reports that the SQLIN structure passed to the executor for the given statement is invalid. The usual way that happens is that the program contains an error that makes it write into data space that is outside of its declared variables, accidently overwriting the SQLIN structure for the SQL statement mentioned. This can happen with subscripts out of range when accessing a COBOL table, or passing a variable to a subprogram that is shorter than the subprogram expects, and possibly in other ways I am not thinking of right now. If you have not carefully checked your program for that kind of error, try to look into that possibility.

If there is some sequence of executing SQL statements that can cause error 8100 to occur, I am not aware of that. There seems not to be any mention of that in the Considerations for the description in the SQL/MP Reference manual of the statements you show in your partial program. Your program appears to be inserting into the same table that has an open cursor, which raises the possibility that this would invalidate the cursor, but there seems not to be any warning about that in the manual, and if that was, in fact, the cause of the problem, I would expect a different error message, since that would be invalidating the cursor, not causing the SQLIN structure passed to the executor to be overwritten.

If you do not find the problem fairly quickly, my suggestion would be to create a test program that contains the bare minimum additional code beyond what you showed, that will perform the logic of scanning the table and inserting the records from the current month back into the table with a new sequence number. If that small test program works (on test data, of course), that would be a strong indication that your large program is writing into storage outside your declared variables, and you'll need to look more carefully to find where it is doing so.

Assuming you can run the large program in Inspect, you could set a write access breakpoint on the start of the SQLIN structure for the FETCH statement and let Inspect help you find where the overwrite is coming from. I know I have seen a description somewhere about how to find the location of the structures passed to the executor for a given SQL statement. It might be in the SQL/MP Programming manual for COBOL, but I'm not sure that is where I saw it. Someone else who sees this comment might remember more clearly that I do where it is described.

If you create the small test program I described two paragraphs back, and it get the error 8100, a way to solve the program might be to change the cursor to allow updates and use UPDATE ... WHERE CURRENT to update the records to change their sequence number. If your program still needs those records to be present with the sequence number of 1, maybe you could insert them into another table before doing the UPDATE, and have the rest of the program access them from that other table.

Good luck!

[Adam “Adamlivinlife” Kamal]

Post by Keith Dick

Post by Randall

Post by Adam “Adamlivinlife” Kamal
Hello Everyone
I am getting SQL error 8100 on a FETCH in a Cobol program. It's sales history table so it has millions of record. The record has a sequence number as a part of the key, SEQ-NUM = 1 indicate the current month record.
On the beginning of each month, after fetching the record, program will do MAX (SEQ-NUM) and reinsert the record with the highest SEQ-NUM, it.
It looks like these insert while FETCHING causes SQL 8100.
My coworker suggested to close the cursor and reopen it after each read/insert (after saving the position of record).
The table has ~1 million records and just did not think open and close the cursor a million times is the best approach. Any idea how to process these kind of history.
Any idea on how to properly process these records other than close and reopen for each record will be highly appreciated.
EXEC SQL
DECLARE GET_SALES_HISTORY CURSOR FOR
SELECT
WARE
, PART_NUM
, REPORT_DATE
,SALES
FROM =HISTORY
WHERE SEQ_NUM = 1
BROWSE ACCESS
END-EXEC.
/
PROCEDURE DIVISION.
EXEC SQL
FETCH GET_HISTORY
INTO :WARE
,:PART-NUM
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
END-EXEC
IF SQLCODE = 0
MOVE REPORT-DATE O TO HV-REPORT-DATE
IF HV-REPORT-DATE-MM NOT = SYS-MM
EXEC SQL
SELECT MAX(SEQ-NUM)
INTO :HV-MAX-SEQ-NUM
FROM =HISTORY
WHERE WARE = :WARE
AND PART_NUM = :PART-NUM
BROWSE ACCESS
END-EXEC
IF SQLCODE = 0
ADD 1 TO HV-MAX-SEQ-NUM
EXEC SQL
INSERT INTO =HISTORY
( WARE
, PART_NUM
, MAX_SEQ_NUM
, REPORT_DATE
, SALES
)
VALUES
( :WARE
,:PART-NUM
,:HV-MAX-SEQ-NUM
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
)
END-EXEC
END-IF
END-IF
END-IF
EXIT-SECTION
EXIT

First, you need to worry about TMF or SQL transactions in all this. That's always a consideration.
BEGIN WORK;
INSERT INTO =HISTORY
( WARE
, PART_NUM
, MAX_SEQ_NUM
, REPORT_DATE
, SALES
)
VALUES
( :WARE
, :PART-NUM
(SELECT MAX(SEQ-NUM)
FROM =HISTORY
WHERE WARE = :WARE
AND PART_NUM = :PART-NUM)
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
)
COMMIT WORK;
or something along those lines. You may need to use multiple correlation ids and multiple host variables. This does work in SQL/MX, but I'm not sure about SQL/MP (it's been a while).
-Randall

I am assuming SQL/MP.
I am assuming the line FETCH GET_HISTORY should be FETCH GET_SALES_HISTORY, since GET_SALES_HISTORY is the name
given for the cursor.
I am assuming that in the line MOVE REPORT-DATE O TO HV-REPORT-DATE , the O standing by itself is a typo and should not be there.
The error 8100 reports that the SQLIN structure passed to the executor for the given statement is invalid. The usual way that happens is that the program contains an error that makes it write into data space that is outside of its declared variables, accidently overwriting the SQLIN structure for the SQL statement mentioned. This can happen with subscripts out of range when accessing a COBOL table, or passing a variable to a subprogram that is shorter than the subprogram expects, and possibly in other ways I am not thinking of right now. If you have not carefully checked your program for that kind of error, try to look into that possibility.
If there is some sequence of executing SQL statements that can cause error 8100 to occur, I am not aware of that. There seems not to be any mention of that in the Considerations for the description in the SQL/MP Reference manual of the statements you show in your partial program. Your program appears to be inserting into the same table that has an open cursor, which raises the possibility that this would invalidate the cursor, but there seems not to be any warning about that in the manual, and if that was, in fact, the cause of the problem, I would expect a different error message, since that would be invalidating the cursor, not causing the SQLIN structure passed to the executor to be overwritten.
If you do not find the problem fairly quickly, my suggestion would be to create a test program that contains the bare minimum additional code beyond what you showed, that will perform the logic of scanning the table and inserting the records from the current month back into the table with a new sequence number. If that small test program works (on test data, of course), that would be a strong indication that your large program is writing into storage outside your declared variables, and you'll need to look more carefully to find where it is doing so.
Assuming you can run the large program in Inspect, you could set a write access breakpoint on the start of the SQLIN structure for the FETCH statement and let Inspect help you find where the overwrite is coming from. I know I have seen a description somewhere about how to find the location of the structures passed to the executor for a given SQL statement. It might be in the SQL/MP Programming manual for COBOL, but I'm not sure that is where I saw it. Someone else who sees this comment might remember more clearly that I do where it is described.
If you create the small test program I described two paragraphs back, and it get the error 8100, a way to solve the program might be to change the cursor to allow updates and use UPDATE ... WHERE CURRENT to update the records to change their sequence number. If your program still needs those records to be present with the sequence number of 1, maybe you could insert them into another table before doing the UPDATE, and have the rest of the program access them from that other table.
Good luck!

Thank you so much for your help. It turned our to be a Cobol table subscript issue that was writing out of the table range.
By just enlarging the table, issue was resolved. Thanks again for your time and help!

[Keith Dick]

Post by Keith Dick

Post by Randall

Post by Adam “Adamlivinlife” Kamal
Hello Everyone
I am getting SQL error 8100 on a FETCH in a Cobol program. It's sales history table so it has millions of record. The record has a sequence number as a part of the key, SEQ-NUM = 1 indicate the current month record.
On the beginning of each month, after fetching the record, program will do MAX (SEQ-NUM) and reinsert the record with the highest SEQ-NUM, it.
It looks like these insert while FETCHING causes SQL 8100.
My coworker suggested to close the cursor and reopen it after each read/insert (after saving the position of record).
The table has ~1 million records and just did not think open and close the cursor a million times is the best approach. Any idea how to process these kind of history.
Any idea on how to properly process these records other than close and reopen for each record will be highly appreciated.
EXEC SQL
DECLARE GET_SALES_HISTORY CURSOR FOR
SELECT
WARE
, PART_NUM
, REPORT_DATE
,SALES
FROM =HISTORY
WHERE SEQ_NUM = 1
BROWSE ACCESS
END-EXEC.
/
PROCEDURE DIVISION.
EXEC SQL
FETCH GET_HISTORY
INTO :WARE
,:PART-NUM
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
END-EXEC
IF SQLCODE = 0
MOVE REPORT-DATE O TO HV-REPORT-DATE
IF HV-REPORT-DATE-MM NOT = SYS-MM
EXEC SQL
SELECT MAX(SEQ-NUM)
INTO :HV-MAX-SEQ-NUM
FROM =HISTORY
WHERE WARE = :WARE
AND PART_NUM = :PART-NUM
BROWSE ACCESS
END-EXEC
IF SQLCODE = 0
ADD 1 TO HV-MAX-SEQ-NUM
EXEC SQL
INSERT INTO =HISTORY
( WARE
, PART_NUM
, MAX_SEQ_NUM
, REPORT_DATE
, SALES
)
VALUES
( :WARE
,:PART-NUM
,:HV-MAX-SEQ-NUM
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
)
END-EXEC
END-IF
END-IF
END-IF
EXIT-SECTION
EXIT

First, you need to worry about TMF or SQL transactions in all this. That's always a consideration.
BEGIN WORK;
INSERT INTO =HISTORY
( WARE
, PART_NUM
, MAX_SEQ_NUM
, REPORT_DATE
, SALES
)
VALUES
( :WARE
, :PART-NUM
(SELECT MAX(SEQ-NUM)
FROM =HISTORY
WHERE WARE = :WARE
AND PART_NUM = :PART-NUM)
,:REPORT-DATE TYPE AS DATETIME YEAR TO MONTH
,:SALES
)
COMMIT WORK;
or something along those lines. You may need to use multiple correlation ids and multiple host variables. This does work in SQL/MX, but I'm not sure about SQL/MP (it's been a while).
-Randall

I am assuming SQL/MP.
I am assuming the line FETCH GET_HISTORY should be FETCH GET_SALES_HISTORY, since GET_SALES_HISTORY is the name
given for the cursor.
I am assuming that in the line MOVE REPORT-DATE O TO HV-REPORT-DATE , the O standing by itself is a typo and should not be there.
The error 8100 reports that the SQLIN structure passed to the executor for the given statement is invalid. The usual way that happens is that the program contains an error that makes it write into data space that is outside of its declared variables, accidently overwriting the SQLIN structure for the SQL statement mentioned. This can happen with subscripts out of range when accessing a COBOL table, or passing a variable to a subprogram that is shorter than the subprogram expects, and possibly in other ways I am not thinking of right now. If you have not carefully checked your program for that kind of error, try to look into that possibility.
If there is some sequence of executing SQL statements that can cause error 8100 to occur, I am not aware of that. There seems not to be any mention of that in the Considerations for the description in the SQL/MP Reference manual of the statements you show in your partial program. Your program appears to be inserting into the same table that has an open cursor, which raises the possibility that this would invalidate the cursor, but there seems not to be any warning about that in the manual, and if that was, in fact, the cause of the problem, I would expect a different error message, since that would be invalidating the cursor, not causing the SQLIN structure passed to the executor to be overwritten.
If you do not find the problem fairly quickly, my suggestion would be to create a test program that contains the bare minimum additional code beyond what you showed, that will perform the logic of scanning the table and inserting the records from the current month back into the table with a new sequence number. If that small test program works (on test data, of course), that would be a strong indication that your large program is writing into storage outside your declared variables, and you'll need to look more carefully to find where it is doing so.
Assuming you can run the large program in Inspect, you could set a write access breakpoint on the start of the SQLIN structure for the FETCH statement and let Inspect help you find where the overwrite is coming from. I know I have seen a description somewhere about how to find the location of the structures passed to the executor for a given SQL statement. It might be in the SQL/MP Programming manual for COBOL, but I'm not sure that is where I saw it. Someone else who sees this comment might remember more clearly that I do where it is described.
If you create the small test program I described two paragraphs back, and it get the error 8100, a way to solve the program might be to change the cursor to allow updates and use UPDATE ... WHERE CURRENT to update the records to change their sequence number. If your program still needs those records to be present with the sequence number of 1, maybe you could insert them into another table before doing the UPDATE, and have the rest of the program access them from that other table.
Good luck!

Thank you so much for your help. It turned our to be a Cobol table subscript issue that was writing out of the table range.
By just enlarging the table, issue was resolved. Thanks again for your time and help!

You are welcome. I am glad that my comments helped. And thank you for posting to tell us how you solved the problem.