ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

RUNSQL Failed

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

  • RUNSQL Failed

    My first attempt to run SQL from a CL program, but it fails. Any ideas ?

    CL CODE
    Code:
              DCL        VAR(&SQL_STMT) TYPE(*CHAR) LEN(5000)
    
       CHGVAR     VAR(&SQL_STMT) VALUE('INSERT INTO +              
                             MYLIB/DRTESTFD SELECT +                      
                             CHAR(DATE(TIMESTAMP_FORMAT(MBRDAT,''YYMMDD'+  
                             ')),ISO) ,TIME(''00:00:00'') + +              
                             CAST(MBRTIM AS DEC(6,0)) , MBLIB, MBFILE, +    
                             MBNRCD, MBTXT, MBFTYP FROM +                  
                             MYLIB/DRTESTFDX WHERE MBNRCD > 0')          
    
    (line 76.00)   RUNSQL     SQL(&SQL_STMT)

    JOB LOG:
    Code:
     Buffer length longer than record for member DRTESTFD.    
     Member DRTESTFD not journaled to journal *N.            
     DRTESTFD in MYLIB not valid for operation.            
     RUNSQLSTM or RUNSQL command failed.                      
     SQL9010 received by DRTESTC2 at 7600. (C D I R)          
     D                                                        
     Processing command failure, refer to job log for details.
    STRSQL Query that I copied into the CL works fine:
    Code:
    insert into MYLIB/drtestfd                                  
    SELECT char(date(timestamp_format(MBRDAT,'YYMMDD')),iso) ,    
    TIME('00:00:00') + CAST(mbrtim AS DEC(6,0)) , MBLIB, MBFILE,  
    mbnrcd, mbtxt, mbftyp FROM MYLIB/drtestfdx

  • #2
    Hit help on the message that says "DRTESTFD in MYLIB not valid for operation." to determine why it isn't valid for what you're doing.

    Comment


    • #3
      I think it is clear enough.
      No journal.

      Run the sentence with COMMIT=*NONE or start journaling the table DRTESTFD

      Comment


      • #4
        Interesting. It says the file needs to be journaled. ?!?!

        Message . . . . : DRTESTFD in MYLIB not valid for operation.

        Cause . . . . . : The reason code is 3. Reason codes are:
        1 -- DRTESTFD has no members.
        2 -- DRTESTFD has been saved with storage free.
        3 -- DRTESTFD not journaled, no authority to the journal, or the journal
        state is *STANDBY. Files with an RI constraint action of CASCADE, SET NULL,
        or SET DEFAULT must be journaled to the same journal.
        4 and 5 -- DRTESTFD is in or being created into production library but the
        user has debug mode UPDPROD(*NO).

        Comment


        • #5
          Originally posted by inigo.redin View Post
          i think it is clear enough.
          No journal.

          Run the sentence with commit=*none or start journaling the table drtestfd
          Thanks !

          Comment

          Working...
          X