HELP: SQL file Authorization Error - OCL to CL to RPGSQLE to Embedded??

[ebbhitch]

OK. I'll try to summarize this clearly. I'm completely stumped at what I did wrong or what factor I'm not aware of missing.

I'm working on an evaluation program that is not yet live. It is in my Sandbox library.
As it is being tested, I originally set it up to update two files within my Sandbox, one taking snapshots of before and after data of accounts evaluated for review (larger file maybe 80 records avg added per evaluation run)and the other as a numerical tally of the scenarios found (so all numbers - 1 record added per evaluation run).

Originally inside the Live Process:

1. OPM Procedure is called
2. At the very end, I tacked on a call to CL wrapper pgm I created.
3. The wrapper adds my Sandbox to the user's Library List, but not as the primary library
4. OVRDBF is used for the live files being evaluated to be sure they are looking at the Live library
5. My SQLRPGLE program is called.
6. The Program has an embedded SQL INSERT statement to update both logging files.
7. Two other new files are referenced within the Sandbox with SELECT statements only.

[D.Abramowitz]

Please post some of the code that is accessing the file in question.

Dave

[ebbhitch]

I'm going to try and simplify/generalize names, but if this isn't what you're asking for, let me know.

Part 1: No changes here

• Library LIVELIB - MENU calls OCL Procedure
• OCL Procedure's last line calls my CL wrapper program EVALWRAP

Part 2a: Wrapper Original Code - to use SPEVALOG which still resided in MYSNDBOX, same as the pgm.

PGM
ADDLIBLE   LIB(MYSNDBOX) POSITION(*BEFORE SECNDLIB)
SNDPGMMSG  MSG('Job started.')
OVRDBF     FILE(ACCTMAST) TOFILE(QS36F/ACCTMAST)
OVRDBF     FILE(ORDMAST) TOFILE(QS36F/ORDMAST)
CALL       PGM(MYSNDBOX/EVALPGM)
SNDPGMMSG  MSG('Job ended.')
DLTOVR     FILE(ACCTMAST)
DLTOVR     FILE(ORDMAST)
RMVLIBLE   LIB(MYSNDBOX)
ENDPGM

Part 2b: Wrapper Code - to use SPEVALOG where it will reside when everything is Live.

PGM
ADDLIBLE   LIB(MYSNDBOX) POSITION(*BEFORE SECNDLIB)
SNDPGMMSG  MSG('Job started.')
OVRDBF     FILE(ACCTMAST) TOFILE(QS36F/ACCTMAST)
OVRDBF     FILE(ORDMAST) TOFILE(QS36F/ORDMAST)
OVRDBF     FILE(SPEVALOG) TOFILE(QS36F/SPEVALOG)
CALL       PGM(MYSNDBOX/EVALPGM)
SNDPGMMSG  MSG('Job ended.')
DLTOVR     FILE(ACCTMAST)
DLTOVR     FILE(ORDMAST)
DLTOVR     FILE(SPEVALOG)
RMVLIBLE   LIB(MYSNDBOX)
ENDPGM

Part 3: Within MYSNDBOX/EVALPGM - which is SQLRPGLE
The query did not change. The file was not defined in F specs because it is only used within the SQL statement.

C/exec sql
     C+          SET OPTION COMMIT = *NONE
     C/end-exec
....
     C/EXEC SQL
     C+        INSERT INTO SPEVALOG (RUNDATE,
     C+                       Z2_non, Z2_DASreg, Z2_DASext, Z3_non, Z3_DASreg, Z3_DASext,
     C+                       Z4_8DASreg, Z4_8DASext, cntOther,
     C+                       Tot_ForceG, Tot_Preset, Tot_Changed, Tot_Read, Tot_Skipped, Tot_PLReg,
     C+                       Tot_UPSEval, Tot_UPSAlt, Tot_PPEval, Tot_PPAlt, Tot_MaxOut)
     C+               VALUES (CURDATE(), 
     C+                       :z2_Reg, :z2_DASr, :z2_DASx, :z3_Reg, :z3_DASr, :z3_DASx,
     C+                       :z4_8_DASr, :z4_8_DASx, :rptCNTothr,
     C+                       :FORCEcnt, :SPSETcnt, :SPCHGcnt, :READcnt, :SKIPcnt, :PLREGcnt,
     C+                       :UPSEVLcnt, :UPSCHGcnt, :PPEVLcnt, :PPCHGcnt,:CSTLIMcnt)
     C/END-EXEC

I had not touched Object Authority until I found this issue.
When the program was updating SPEVALOG within the same library as the program, the end user's process always updated the file. It stopped working after trying to work with the file within QS36F instead. I made the Object Authority's identical on Thursday for SPEVALOG within both libraries, giving the specific users *ALL rights and giving *PUBLIC specific rights, instead of *EXCLUDE. However, the users' job logs are still showing the original error messages.

                         Object    ---------------Data---------------  
User        Group       Authority  Read  Add  Update  Delete  Execute  
*PUBLIC                 USER DEF    X     X     X                X     
MYSELF                    *ALL      X     X     X       X        X     
USER1                     *ALL      X     X     X       X        X     
USER2                     *ALL      X     X     X       X        X     
USER3                     *ALL      X     X     X       X        X     
USER4                     *ALL      X     X     X       X        X

It looks like I did create the file in QS36F by using CREATE TABLE scripts through the SQuirreL tool. I'm still trying to compare the files from either the iSeries or the SQuirrel tool to find any differences.

Does any of this this help at all??
Thank you!
Elizabeth

[D.Abramowitz]

Is your CL program compiled as a CLP, or CLLE?

If the program is CLLE try recompiling as CLP and see if the results are any different.

[ebbhitch]

It is already compiled as a CLP.

I even tried adding a record to the file in QS36F via DBU in case having the member be empty was the issue.

Is there another place to set authority on the iSeries for files created by SQL Create scripts vs physical files created through a 5250 session?

I may try to replace the SQL Created file with using CPYF from my sandbox to QS36F, and if that works, great, but I'd still hate to not understand what is really going on here.

I'll let you know if something new works. Any other insights or suggestions are welcome.

Thank you!

[ebbhitch]

Aaaand, I copied SPEVALOG in QS36F to SPEVALOG2 and described it as the SQL CREATEd version.
I then copied SPEVALOG from My Sandbox to QS36F via PDM including the data..
and the next user's run attempt looks to have run cleanly:

CALL PGM(MYSNDBOX/SUREPOSTCL)                    
Library MYSNDBOX added to library list.          
Job started.                                    
DSPLY  SQLPGM read 275 orders. 22 new method.
Job ended.                                      
Library MYSNDBOX removed from library list.

I can see a new entry for today within the QS36F file now as well.

So it works for the moment, but I'm still completely baffled as to why creating the table through SQL instead of CPYF caused problems. Maybe someday the explanation will show itself..

Thank you for your time!