MVSFORUMS.com

[Jamylady]

Hi,
I have a CICS program which is been activated by MQ series. This CICS program access DB2 database with the following query
SELECT * FROM CONTRACT
INTO :DNT-CONTRACT
WHERE CONTRACT+NO = :CONTRACT-NO
AND RPT_YR = :RPT-YR


We had a table change for the CONTRACT table to add a new column at the last (it is a date filed with not null), but this program was not compiled.
More over, this program is not using this new filed also.
There is no Insert, no update statement, just a simple SELECT.
Program is checking for SQLCODE 000 and 100. If SQLCODE is other than '000' or '100', it is moving 'W00005' code to the output data area.

Now the question is that when users accessing this program using their interface, they get ''W00005' . So I have no clue of what SQLCODE it is.
So would it be possible for us guess any thing out of this situation?
I was thinking that this error is because program was not recompiled and bind after the table change. But in that scinario, what could be the return code? I try simulating that in a program. I removed the last field from a DCLGEN and compile, bind and run the program. I am getting '000' after executing the SELECT sql.
SELECT * FROM CONTRACT
INTO :DNT-CONTRACT
WHERE CONTRACT+NO = :CONTRACT-NO
AND RPT_YR = :RPT-YR

I have removed last column from DNT-CONTRACT group. so the acutual number of colums selected from the table is 15, and number of host variable under DNT-CONTRACT is 14. But after the execution, i am getting 100.

So I am just wondering whether recmopile and rebind would really solve my problem?
could any one help?
Thanks
JA

[dbzTHEdinosauer]

change the 'SELECT *' to SELECT COL_A, COL_B ....

then unless you need a new column you don't have to worry about your program. That is one of the reasons 'SELECT *' is poor programming practice. It INSURES that the SQL will NEVER work when you add a new column.
_________________
Dick Brenholtz
American living in Varel, Germany

[Jamylady]

Thank you
But is that the reason of the problem we have? I didnt get any sqlcode other than 000 or 100 by executing that SQL. Does it have any difference in Batch and Online?

[kolusu]

Jamylady,

The reason is simple. Your select * selected the entire row and was moving into a single working storage variable causing the data overflow. The data just corrupted the fields below the definition of DNT-CONTRACT. I guess SQLCA is a defined underneath the DNT-CONTRACT. The data from the select statement overflowed and corrupted the sqlca also.

As dbzTHEdinosauer mentioned select * is a poor programming practise. Remove that just select the column you are interested in.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu