iSeries database update via CL

5 pts.
Tags:
iSeries database
JOBQ
SQL
Hi! I have an iSeries database table that has a column name JOBQ. I want to update all records with JOBQ value QBATCH into 2 different jobq names for alternate records. I'm a CL programmer and looking for an example of updating a column via SQL that I can call from my CL in a loop.

Software/Hardware used:
P6 V7R1

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: 7  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
  • ToddN2000
    Do you only use CL? You mention changing to two different JOBQS. Is this so more jobs can run concurrently?
    15,605 pointsBadges:
    report
  • ToddN2000
    How is this table being built? If it's from jobs currently in a JOBQ it may be possible that by the time this job runs some jobs may have been completed and are no longer there. Defeating your intent. What is the reason for the split ?
    15,605 pointsBadges:
    report
  • bvining

    Here is a sample CL program that updates records in the file JOBS where the JOBQ field is set to QBATCH. The program alternately sets the JOBQ field to THISONE and THATONE. The CL program loops through all records of JOBS and uses SQL to update the records with JOBQ = QBATCH. The program assumes there is a value in each record (in my case KeyFld) that uniquely identifies each record in JOBS. KeyFld is defined in JOBS as decimal 9,0.

                 Pgm                                               
                 DclF       File(BVining/Jobs)                     
                                                                   
                 Dcl        Var(&EveryOther) Type(*Lgl)            
                 Dcl        Var(&KeyFldChr)  Type(*Char) Len(9)    
                 Dcl        Var(&SQLStmt)    Type(*Char) Len(512)  
                                                                   
     Loop:       RcvF                                              
                 MonMsg     MsgID(CPF0864) Exec(Goto CmdLbl(Exit)) 
                                                                   
                 If         Cond(&Jobq *EQ 'QBATCH') Then(Do)      
                            If Cond(*Not &EveryOther) Then( +      
                               ChgVar Var(&JobQ) Value('THISONE')) 
                            Else Cmd( +                            
                               ChgVar Var(&JobQ) Value('THATONE')) 
                                                                   
                            ChgVar Var(&KeyFldChr) Value(&KeyFld)          
                            ChgVar Var(&SQLStmt) Value(+                   
                               'Update BVining/Jobs Set JobQ = ''' *TCat + 
                               &JobQ *TCat +                               
                               ''' where KeyFld =' *BCat +                 
                               &KeyFldChr)                                 
                            RunSQL SQL(&SQLStmt) Commit(*None)             
                            ChgVar Var(&EveryOther) Value(*Not &EveryOther)
                            EndDo                                          
                 Goto       CmdLbl(Loop)                                   
     Exit:       EndPgm                                                    

     

     

    6,620 pointsBadges:
    report
  • philpl1jb

    Bruce

    It seems that our editor cut your entry off.

    Phil

    51,355 pointsBadges:
    report
  • bvining
    Phil,

    It looks like it's all there, just the code insert logic of my reply certainly threw in a few line breaks that were not expected... I would have expected it to at least go 80-wide.
    6,620 pointsBadges:
    report
  • philpl1jb

    Well, yo lern somtin evey day ..

    Cut and paste it and all the stuff I couldn't see is there. 

    Great work Bruce.

     ChgVar Var(&EveryOther) Value(*Not &EveryOther)  .. brilliant.

     

    51,355 pointsBadges:
    report
  • ToddN2000
    Slick code bvining...Will have to give it a try in some other applications...
    15,605 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