Differing Results With SBMJOB Command

45 pts.
Tags:
SBMJOB
truncating
We are having a problem using SBMJOB to run a long EXECUTE SQL command string on one of our environments.  When we run the string from the command line, it runs.  When we embed it in the SBMJOB command, it fails with a message indicating a missing apostrophe.  Looking at the job log, the command seems to have been truncated.  What doesn't make sense is that the command will run as SBMJOB on our test system and another production system.  So our thought is that there is something different in the configuration of this one system, but we don't know what to look at.  All systems are still v5r4 

Answer Wiki

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

Discuss This Question: 11  Replies

 
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
  • philpl1jb

    Typically batch queries are either

    - put into a text member. The sbmjob can CALL A CL or directly use RUNSQLSTM to process the statement

    - created using WRKQMQRY and run STRQMQRY.

    49,590 pointsBadges:
    report
  • rweber
    We are using a SEQUEL command: SEQUEL/EXECUTE SQL...
    45 pointsBadges:
    report
  • CharlieBrowne

    If you have apostrophes within the SEQUEL Statement, when you do the SBMJOB, you need to make every imbedded apostrophe two apostrophes



    41,370 pointsBadges:
    report
  • rweber
    We have confirmed the syntax and all embedded apostrophes.  The same exact command string (copied, pasted) works on 2 machines, but not a third.  That's why we are trying to understand what is different about this one system.
    45 pointsBadges:
    report
  • TomLiotta
    We need to know what the command is before we can make any useful guesses. Without knowing what's being attempted, the possible answers include essentially every error that a pseudo-SQL statement can return. It's probably related to authorities or database definition differences or user definitions or PTFs or the configuration or setup of the Sequel product, but it could be other things. -- Tom
    125,585 pointsBadges:
    report
  • rweber

    The command string is below.  We were able to get it to run after removing the file references since the variables were all unique.  This shortened the string and the command ran.

    SBMJOB CMD(SEQUEL/EXECUTE SQL('SELECT rdat.rtptrhs COLHDG("Transaction Date") EDTCDE(W), rist.rtptrhs COLHDG("Demand Store") EDTCDE(X), rdst.rtptrhs COLHDG("Shipped By Store") EDTCDE(X), idiv.ipithdr COLHDG("Division nbr") EDTCDE(X),idpt.ipithdr COLHDG("DPT nbr") EDTCDE(X), rcls.rtptrhs COLHDG("Class") EDTCDE(X), rven.rtptrhs COLHDG("Vendor") EDTCDE(X), rsty.rtptrhs COLHDG("Style") EDTCDE(X), rclr.rtptrhs COLHDG("Color") EDTCDE(X), rsiz.rtptrhs COLHDG("Size") EDTCDE(X), rvst.rtptrhs COLHDG("Vendor Style"), ides.ipithdr COLHDG("Item Desc"), rqty.rtptrhs COLHDG("QTY Sold"), rsal.rtptrhs COLHDG("Extended Sale Price"), rsps.rtptrhs COLHDG("Sales Person/" "Employee"), rcash.rtptrhs COLHDG("Cashier"), rreg.rtptrhs COLHDG("Register#"), rent.rtptrhs COLHDG("Transaction#") FROM *libl/RTPTRHS,*libl/IPITHDR INNER JOIN rcls.rtptrhs=icls.ipithdr and rven.rtptrhs=iven.ipithdr and rsty.rtptrhs=isty.ipithdr and rclr.rtptrhs=iclr.ipithdr and rsiz.rtptrhs=isiz.ipithdr WHERE rreg.rtptrhs<>"00" and rist.rtptrhs<>rdst.rtptrhs and rist.rtptrhs>0 and rdst.rtptrhs>0 and rdat.rtptrhs=20131217') PCFMT(*XLS) REPLACE(*YES) RECIPIENT(DIMI) EMLMSG('Sales Date: 2013-12-17') TEXT('<PRC> X-Store Send Sale Daily report'))  

    45 pointsBadges:
    report
  • TomLiotta
    Since Sequel is a 3rd-party product, it's almost certain that there is some difference in the product or the product configuration on the one system. It's also possible that system DB2 PTFs are different on the one system. Assuming it was actually the same command on all systems and all are V5R4, there seems to be no reason why any shortening of the command string would affect just the one system. I suggest contacting Help/Systems Support and reporting the issue. It could affect future results in potentially unpredictable ways. -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Not that this helps.
    SBMJOB CMD(SEQUEL/EXECUTE SQL(
             1         2         3         4         5 
    12345678901234567890123456789012345678901234567890
    
    'SELECT rdat.rtptrhs COLHDG("Transaction Date") ED
    TCDE(W), rist.rtptrhs COLHDG("Demand Store") EDTCD   100
    E(X), rdst.rtptrhs COLHDG("Shipped By Store") EDTC
    DE(X), idiv.ipithdr COLHDG("Division nbr") EDTCDE(   200
    X),idpt.ipithdr COLHDG("DPT nbr") EDTCDE(X), rcls.
    rtptrhs COLHDG("Class") EDTCDE(X), rven.rtptrhs CO   300
    LHDG("Vendor") EDTCDE(X), rsty.rtptrhs COLHDG("Sty
    le") EDTCDE(X), rclr.rtptrhs COLHDG("Color") EDTCD   400
    E(X), rsiz.rtptrhs COLHDG("Size") EDTCDE(X), rvst.
    rtptrhs COLHDG("Vendor Style"), ides.ipithdr COLHD   500
    G("Item Desc"), rqty.rtptrhs COLHDG("QTY Sold"), r
    sal.rtptrhs COLHDG("Extended Sale Price"), rsps.rt   600
    ptrhs COLHDG("Sales Person/" "Employee"), rcash.rt
    ptrhs COLHDG("Cashier"), rreg.rtptrhs COLHDG("Regi   700
    ster#"), rent.rtptrhs COLHDG("Transaction#") FROM 
    *libl/RTPTRHS,*libl/IPITHDR INNER JOIN rcls.rtptrh   800
    s=icls.ipithdr and rven.rtptrhs=iven.ipithdr and r
    sty.rtptrhs=isty.ipithdr and rclr.rtptrhs=iclr.ipi   900
    thdr and rsiz.rtptrhs=isiz.ipithdr WHERE rreg.rtpt
    rhs<>"00" and rist.rtptrhs<>rdst.rtptrhs and rist.  1000
    rtptrhs>0 and rdst.rtptrhs>0 and rdat.rtptrhs=2013
    1217'
    
    ) PCFMT(*XLS) REPLACE(*YES) RECIPIENT(DIMI) E  1100
    MLMSG('Sales Date: 2013-12-17') TEXT('<PRC> X-Stor
    e Send Sale Daily report'))                         1200  
    
    49,590 pointsBadges:
    report
  • philpl1jb

    Assuming that all three systems have SEQUEL/EXECUTE

    and that all the installations allow the keywords SQL, PCFMT, REPLACE, RECIPIENT, EMLMSG, and Text then this command should work on all three systems.

    But the cut and paste .. paste from what .. sometimes word processors (microsoft) is happy to change ' ' or " " to left ' and right ' or left " and right ".  which would be errors.

     

    Although the sbmjob command can be up to 20,000 characters, I don't see how you paste this entire command in.  But then I don't have sequl to try pasting into the sequl prompt.

    49,590 pointsBadges:
    report
  • rweber
    We talked to Help/Systems.  They think the recall something like this but apparently no one bothered to document it.  Our developer came up with a different way to do this and that's what got promoted.  I suggested more efficient ways to handle the task, but I could only lead him to the water...

    Thanks to all for your help.
    45 pointsBadges:
    report
  • TomLiotta
    As posted, the command pastes into a QCMD command window if F11='Display full' is set. I'd press Help/Systems for a fix. -- Tom
    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