AS400, SQL and COBOL anomalies

75 pts.
Tags:
AS/400
COBOL
Embedded SQL
iSeries
I coded an update query in COBOL and included COMMIT=NONE so the update could go through. When the COBOL program executed it returned nulls, and coalesced the value to the default. When the query was executed either through qmqry or on-line on the sql screen it worked fine and updated the field properly.

I also tried calling a cl program that executed the QMQRY from within the COBOL program and I also got bad results. When I called the CL from within another CL or called the CL program directly I got expected results.

Here is the query:

EXEC SQL UPDATE FILEA A SET A.ITMID = COALESCE((SELECT DISTINCT B.ITMID FROM FILEB B WHERE A.FLDA = B.FLDA AND A.FLDB = B.FLDB AND A.FLDC = B.FLDC),:NO-ITM) END-EXEC.



Software/Hardware used:
as400 cobol iseries embedded sql

Answer Wiki

Thanks. We'll let you know when a new response is added.

Are you sure the library list is the same?
Is the COBOL program doing any overrides?
I’d do a CHGJOB LOG(4 00 *SECLVL) and run the COBOL program again.
Then check your joblog to see what shows up there.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Hard to answer for multiple reasons. Please describe the COBOL -- ILE or OPM? Is it a module in a larger program? What options were specified on the CRTxxx command(s)? What was the SET OPTION statement in the compile module/program? What PROCESS statement was used? Also:
    • UPDATE FILEA A SET A.ITMID = COALESCE((SELECT DISTINCT B.ITMID FROM FILEB B WHERE A.FLDA = B.FLDA AND A.FLDB = B.FLDB AND A.FLDC = B.FLDC),:NO-ITM)
    Since that statement contains a host variable -- NO-ITM -- it wouldn't run in QMQRY nor in interactive SQL. You had to use a different UPDATE statement. What was it? And what is the COBOL definition of NO-ITM? (NO-ITM probably doesn't matter, but it might help in understanding.) Did the COBOL execution actually update rows causing A.ITMID to become null when it previously had a value or was A.ITMID already null?
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following