Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

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

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
  • #1

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

    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.
  • When the program is done, DLTOVR is used, the Sandbox library is removed.


    • Then to start preparing for moving the evaluation program live:
    • I created the numerical log file (SPEVALOG), and the two reference files, ZIPA & ZIPB (for a generic naming). I cannot remember if I used CPYF for these on the iSeries or if I used SQL create statements through SQuirreL, which may be part of the problem.
    • I added an OVRDBF statement to the CL wrapper so SPEVALOG would be looking at the Live Library instead of the Sandbox library.


    After a little while, I realized I was still getting comparison record data in the file in my own sandbox, but no longer getting the statistical breakdown logging in SPEVALOG in the Live FILE.
    I don't run the process ever that hits this process, but I watched their log files yesterday and today and found this in their JobLog:

    Code:
    Job started.                                          
Buffer length longer than record for member SPEVALOG. 
Not authorized to object SPEVALOG in QS36F type *FILE.
Not authorized to object SPEVALOG in QS36F type *FILE.
DSPLY  SQLPGM read 237 orders. 17 new method.      
Job ended.
    Since the process is run a few times a day, I kept increasing Authority to the SPEVALOG for Public and finally gave every user that runs this process *ALL authority and gave PUBLIC *USE authority by the end of the day.

    Today's user's job log had the same messages though in her job log. She has *ALL authority to the file, so I'm completely stumped at what to try next. Or even where to look for more information.

    Please help! I don't know what to even search for anymore!
    Thank you!
    Elizabeth
Tags: None
  • #2
    Please post some of the code that is accessing the file in question.

    Dave

    Comment

    • #3
      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.
      Code:
      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.
      Code:
      PGM
ADDLIBLE   LIB(MYSNDBOX) POSITION(*BEFORE SECNDLIB)
SNDPGMMSG  MSG('Job started.')
OVRDBF     FILE(ACCTMAST) TOFILE(QS36F/ACCTMAST)
OVRDBF     FILE(ORDMAST) TOFILE(QS36F/ORDMAST)
[B][COLOR="#0000FF"]OVRDBF     FILE(SPEVALOG) TOFILE(QS36F/SPEVALOG)[/COLOR][/B]
CALL       PGM(MYSNDBOX/EVALPGM)
SNDPGMMSG  MSG('Job ended.')
DLTOVR     FILE(ACCTMAST)
DLTOVR     FILE(ORDMAST)
[B][COLOR="#0000FF"]DLTOVR     FILE(SPEVALOG)[/COLOR][/B]
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.
      Code:
      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.
      Code:
                               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

      Comment

      • #4
        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.

        Comment

        • #5
          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!

          Comment

          • #6
            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:
            Code:
            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!

            Comment

              • Previous template Next
              Working...
              X