Sponsored Links
Sponsored Link

sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Error during SQL

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Error during SQL

    Hello,

    Not sure if I'm doing something wrong here?

    Process followed;
    DSPMSG MSGQ(AQUEUE) OUTPUT(*PRINT)
    CRTPF FILE(RAKELLR/OVNANALY) RCDLEN(132)
    CPYSPLF FILE(QPDSPMSG) TOFILE(RAKELLR/OVNANALY)
    Used SQL to delete crap like column headings
    As it's 2 rows per message - message text and message details, used SQL to move message details into another column on the same row as the message text this was output to another file: RAKELLR/OVNANALY1

    At this point I have RAKELLR/OVNANALY1 with FIELD1 (the message text, 132 CHAR) and FIELD2 (the message details, 132 CHAR)

    From here I am going to create a final file that has 5 fields; OVSTEP, OVTEXT, OVTMSP, OVDATE, OVTIME

    However, when running the below statement (in STRSQL);
    Code:
    CREATE TABLE RAKELLR/OVNANALY2 AS (       
    SELECT DEC(SUBSTR(FIELD1,25,4 ),4) OVSTEP,
           SUBSTR(FIELD1,41,50) OVTEXT, 
           TIMESTAMP_FORMAT(                  
              SUBSTR(FIELD2,70,8) ||' '||        
              SUBSTR(FIELD2,79,8)               
           ,'DD/MM/YY HH24MISS') OVTMSP,   
           DATE(TIMESTAMP_FORMAT(             
                    SUBSTR(FIELD2,70,8) ||' '||        
                    SUBSTR(FIELD2,79,8)              
                ,'DD/MM/YY HH24MISS')
                ) OVDATE,  
           TIME(TIMESTAMP_FORMAT(             
                    SUBSTR(FIELD2,70,8) ||' '||        
                    SUBSTR(FIELD2,79,8),               
                ,'DD/MM/YY HH24MISS')
                ) OVTIME  
      FROM RAKELLR/OVNANALY1) WITH DATA
    I get these errors in my job log;
    Code:
    Pointer not set for location referenced.              
    User-defined function error on member OVNANALY1.      
    User-defined function error on member OVNANALY1.      
    Trigger program or external routine detected an error.
    Trigger program or external routine detected an error.
    Further details;
    Code:
    Message ID . . . . . . :   CPF503E       Severity . . . . . . . :   30       
    Message type . . . . . :   Sender copy                                       
    Date sent  . . . . . . :   08/10/18      Time sent  . . . . . . :   17:29:06 
    
    Message . . . . :   User-defined function error on member OVNANALY1.         
    Cause . . . . . :   An error occurred while invoking user-defined function   
      TIMESTAMP_FORMAT in library QSYS2. The error occurred while invoking the   
      associated external program or service program QQQSVUSR in library QSYS,   
      program entry point or external name QQQTimestamp_Format, specific name *N.
      The error occurred on member OVNANALY1 file OVNANALY1 in library RAKELLR.  
      The error code is 2. The error codes and their meanings follow:            
        1 -- The external program or service program returned SQLSTATE 00000. The
      text message returned from the program is:  .                              
        2 -- The external program failed before it completed.
    Code:
    Message ID . . . . . . :   SQL0443       Severity . . . . . . . :   30       
    Message type . . . . . :   Diagnostic                                        
    Date sent  . . . . . . :   08/10/18      Time sent  . . . . . . :   17:29:06 
    
    Message . . . . :   Trigger program or external routine detected an error.   
    Cause . . . . . :   Either a trigger program, external procedure, or external
      function detected and returned an error to SQL. If the error occurred in a 
      trigger program, the trigger was on table QQQSVUSR in schema QSYS. If the  
      error occurred in an external procedure or function, the external name is  
      QQQSVUSR in schema QSYS.  The associated text is *N.  If the error occurred
      in a trigger program, the associated text is the type of trigger program.  
      If the error occurred in an external function, the associated text is the  
      text of the error message returned from the external function.
    If I remove the OVTIME column from the above SQL, the statements runs without issue. If I then run the below:

    Code:
    CREATE TABLE RAKELLR/OVNANALY3 AS (   
    SELECT A.*, TIME(OVTMSP) OVTIME       
      FROM RAKELLR/OVNANALY2 A ) WITH DATA
    I have no issues, so this suggests to me that it isn't a data problem? Have I uncovered a bug or am I missing something?

    Cheers,
    Ryan



  • #2
    Maybe I'm the only one - but it would help me to help you if I knew why on earth you were going through such an elaborate process. What exactly are you trying to achieve?

    Comment


    • #3
      Jon,

      It would appear that they need the contents of a message file into a DB table. Maybe for something like change management?

      RDKells,

      Is it possible that a bad date is being fed into the TIMESTAMP_FORMAT function? Do you know the value that is being passed in? That's where I'd start.

      Comment


      • #4
        Originally posted by Scott Klement View Post
        Jon,

        It would appear that they need the contents of a message file into a DB table. Maybe for something like change management?
        Yes - I'd figured out that that appeared to be the intent of the code. But given the number of new SQL interfaces, message APIs etc. I figured that knowing _why_ this was being done might be a better starting point than trying to debug with little information. After all - extracting data from a print report is rarely the best place to start.

        Comment


        • #5
          This is what I was thinking of https://www.ibm.com/developerworks/c...UE_INFO%20View

          This allows the direct processing with SQL of message queue data in the same manner as DSPMSG. So copying from a spool file is not needed.

          Comment


          • #6
            As per Scott's reply; I'm converting a message queue to a DB file, it's not that elaborate just a few commands and some basic SQL - the query was more around the fact that the O/S is throwing a message when trying time() over a timestamp_format() function, surely that isn't right?

            I wasn't really after an alternate method but that link is pretty interesting, unfortunately though "message_queue_info" doesn't exist on the machine I'm looking at (V7R1M0) however it does exist on our development machines (V7R3M0) which is a pain!

            Comment


            • #7
              Scott Klement if a bad date was being fed in, then wouldn't timestamp_format() function fail regardless of if time() was wrapped around it or not? I think I've had it before and I'm sure it just fails and states the field wasn't valid or something like that.

              Comment


              • #8
                There is definitely something wrong here.

                So I copied that SQL, changed the file to be created to OVNANALY4 (as i've used 2 elsewhere now) and after correcting my syntax errors (oops) it worked;

                Code:
                Message ID . . . . . . :   SQL7905       Severity . . . . . . . :   20       
                Message type . . . . . :   Information                                       
                
                Message . . . . :   Table OVNANALY4 in RAKELLR created but was not journaled.
                Cause . . . . . :   The table OVNANALY4 was created in RAKELLR, but the table
                  was not journaled.  Either a QDFTJRN data area indicated that journaling
                I couldn't believe my eyes, so I dropped the table;

                Code:
                Message ID . . . . . . :   SQL7953       Severity . . . . . . . :   00 
                Message type . . . . . :   Information                                 
                
                Message . . . . :   Drop of OVNANALY4 in RAKELLR complete.
                Re-ran it and now it's erroring again, wooohooo!


                Comment


                • #9
                  Originally posted by RDKells View Post
                  Scott Klement if a bad date was being fed in, then wouldn't timestamp_format() function fail regardless of if time() was wrapped around it or not? I think I've had it before and I'm sure it just fails and states the field wasn't valid or something like that.
                  I guess you lost me somewhere... The example you said was failing with the errors didn't use TIME(), it used TIMESTAMP_FORMAT() over something called FIELD2. In a later example, you used TIME() over a different field... but I didn't see that this was causing problems.

                  Comment


                  • #10
                    Originally posted by JonBoy View Post
                    Lets not confuse a message queue with a message file...?

                    Comment


                    • #11
                      I don't think I am confusing anything Scott. The OP originally posted code that included DSPMSG. The introductory text for this new SQL view says explicitly

                      "It returns information similar to what is returned by the Display Messages (DSPMSG)"

                      So what am I missing? The OP also seemed to think it would do the job but he's stuck on 7.1.

                      Comment


                      • #12
                        Originally posted by JonBoy View Post
                        I don't think I am confusing anything Scott. The OP originally posted code that included DSPMSG.
                        You're absolutely right... it is me who got them confused. For some reason I thought it was DSPMSGD rather than DSPMSG. (I think I was confusing this with a different conversation where someone told me he was using message descriptions and wanted to know if they could be used with change management.)

                        Comment

                        sponsored links

                        Collapse

                        Working...
                        X