Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    54

    Unanswered: Update query (first time for everyone)....

    Not quite sure what illegal symbol I'm using. I'm pretty sure this query should work to replace the bad applctn_txt with the correct one.

    Code:
    ---------+---------+---------+---------+---------+---------+---------+---------
    UPDATE                                                                         
      SET PA.APPLCTN_TXT = JX.FULL_SYS_NM_TXT                                      
    FROM TOTDB01.PROD_ABEND PA                                                     
      INNER JOIN TOTDB01.JOB_XREF JX                                               
      ON SUBSTR(PA.JOB_TXT,1,2) = JX.PFX_TXT                                       
    WHERE PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130;                  
    ---------+---------+---------+---------+---------+---------+---------+---------
    DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT   
             BE LEGAL ARE: INCLUDE <END-OF-STATEMENT> QUERYNO WHERE SKIP WITH SET  
    DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
    DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
    DSNT416I SQLERRD    = 3 0  0  -1  81  502 SQL DIAGNOSTIC INFORMATION           
    DSNT416I SQLERRD    = X'00000003'  X'00000000'  X'00000000'  X'FFFFFFFF'       
             X'00000051'  X'000001F6' SQL DIAGNOSTIC INFORMATION                   
    ---------+---------+---------+---------+---------+---------+---------+---------
    DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0                                      
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need the table name right after the UPDATE.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2004
    Posts
    54
    Uhh

    Code:
    ---------+---------+---------+---------+---------+---------+---------+---------
    UPDATE TOTDB01.PROD_ABEND PA                                                   
      SET PA.APPLCTN_TXT = JX.FULL_SYS_NM_TXT                                      
    FROM TOTDB01.PROD_ABEND PA                                                     
      INNER JOIN TOTDB01.JOB_XREF JX                                               
      ON SUBSTR(PA.JOB_TXT,1,2) = JX.PFX_TXT                                       
    WHERE PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130;                  
    ---------+---------+---------+---------+---------+---------+---------+---------
    DSNT408I SQLCODE = -199, ERROR:  ILLEGAL USE OF KEYWORD FROM.  TOKEN ( .       
             MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE WAS EXPECTED   
    DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
    DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
    DSNT416I SQLERRD    = 2 0  0  -1  145  506 SQL DIAGNOSTIC INFORMATION          
    DSNT416I SQLERRD    = X'00000002'  X'00000000'  X'00000000'  X'FFFFFFFF'       
             X'00000091'  X'000001FA' SQL DIAGNOSTIC INFORMATION                   
    ---------+---------+---------+---------+---------+---------+---------+---------
    DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0                                      
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0                      
    ---------+---------+---------+---------+---------+---------+---------+---------

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I assume it is something like this:

    UPDATE TOTDB01.PROD_ABEND PA
    SET PA.APPLCTN_TXT =
    (Select X.FULL_SYS_NM_TXT From .....)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Usually, WHERE clause is required to update only rows matching with JX, like this:

    UPDATE TOTDB01.PROD_ABEND PA
    SET PA.APPLCTN_TXT =
    (Select X.FULL_SYS_NM_TXT From .....)
    WHERE EXISTS
    (Select X.FULL_SYS_NM_TXT From .....)

    or use MERGE statement to avoid duplicated codes in SET clause and in WHERE clause.

  6. #6
    Join Date
    Nov 2004
    Posts
    54
    This is what I came up with and it worked pretty well..


    Code:
    UPDATE TOTDB01.PROD_ABEND PA                          
      SET PA.APPLCTN_TXT =                                
         (SELECT JX.FULL_SYS_NM_TXT                       
          FROM TOTDB01.PROD_ABEND PABD                    
             INNER JOIN TOTDB01.JOB_XREF JX               
             ON SUBSTR(PA.JOB_TXT,1,2) = JX.PFX_TXT       
          WHERE PA.PROD_ABEND_ID = PABD.PROD_ABEND_ID     
          )                                               
    WHERE PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130;

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) So, there is a row of JX which satisfy SUBSTR(PA.JOB_TXT,1,2) = JX.PFX_TXT for all rows of PA which satisfy PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130.

    If there was no such matching row of JX for some rows of PA which satisfy PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130,
    PA.APPLCTN_TXT would be set to null for such rows.

    If there were multiple matching rows of JX for a row of PA which satisfy PA.APPLCTN_TXT='ABLANK' AND PA.PROD_ABEND_ID >= 202130,
    you would get SQL0811N error.

    2) I think that TOTDB01.PROD_ABEND PABD is not necessary.

  8. #8
    Join Date
    Nov 2004
    Posts
    54
    I Made a slight adjustment to the query and it works great.

    As you can see, the first I tell it to update TS and then match TS.TST_SUC_ID = TSUC.TST_SUC_ID to make sure I don't have any dups. Then each line inspect the first 2 characters of the JOB_TXT and change the OWNR_TXT to be the right FULL_SYS_NM_TXT.

    I just updated 4 tables and 19,000,000 records in just under 17mins. Pretty quick too.

    Code:
       UPDATE TOTDB01.TST_SUC TS                                   
         SET TS.OWNR_TXT =                                         
            (SELECT JX.FULL_SYS_NM_TXT                             
             FROM TOTDB01.TST_SUC TSUC                             
                INNER JOIN TOTDB01.JOB_XREF JX                     
                ON SUBSTR(TSUC.JOB_TXT,1,2) = JX.PFX_TXT           
             WHERE TS.TST_SUC_ID = TSUC.TST_SUC_ID     
            );
    Last edited by goldfishhh; 08-14-10 at 08:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •