asked on

DB2 Iseries SQL failing to update multiple records

I am teaching myself sql so I may be asking an elementary question. Please forgive my ignorance.

I have this code :

delete from  VD_GPL.AGNITEMALLOCATIONKEY;


create table qtemp.items(
itemallocationid char(10) default ' '
,itemId char(20) default ' '
,displayproductnumber char(30) default ' '
,INVENTDIM_CONFIGID char(15) default ' '
,dataareaid char(4) default ' ' 
,locationdescription char(30) default' '
,toteinsertid char(10) default '  '
,totesid char(10) default ' '
,unitpertote integer default 0
,configid char(15) default ' '
,inventsiteid char(10) default ' '
,somiitemid char(20) default ' '
,imprfg char(3) default ' '
,rtrcode char(9) default ' ' 
,imsts char(1) default ' ' 
,imtrnr char(10) default ' '
,lmpn char(15) default ' '
,lmcode char(9) default ' '
,imco char(1) default ' ' 
);


insert into qtemp.items 
(
ITEMID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,imco)

(Select DISTINCT ITEMID ,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,imco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco in('1') and lmco in('1') AND
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND  IMPRFG = B.IMPRFG AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID AND IMSTS = B.IMSTS AND LMCODE = C.LMCODE AND RTRCODE = B.IMRTRN AND LMPN = C.LMPN)
);

insert into qtemp.items 
(
ITEMID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,imco)

(Select DISTINCT ITEMID ,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,imco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco in('3') and lmco in('3') AND
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND  IMPRFG = B.IMPRFG AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID AND IMSTS = B.IMSTS AND LMCODE = C.LMCODE AND RTRCODE = B.IMRTRN AND LMPN = C.LMPN)
);

insert into qtemp.items 
(
ITEMID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,imco)

(Select DISTINCT ITEMID ,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,imco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco in('5') and lmco in('5') AND
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND  IMPRFG = B.IMPRFG AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID AND IMSTS = B.IMSTS AND LMCODE = C.LMCODE AND RTRCODE = B.IMRTRN AND LMPN = C.LMPN)
);

insert into qtemp.items 
(
ITEMID, INVENTSITEID,IMPRFG,DISPLAYPRODUCTNUMBER,configid,imsts,lmcode,rtrcode,lmpn,imco)

(Select DISTINCT ITEMID ,'IND',IMPRFG, displayproductnumber, INVENTDIM_CONFIGID, imsts,Lmcode,imrtrn,lmpn,imco
from  AX_DAT.DMFPRODUCTENTITY a
       inner join KBM400MFG.FKITMSTR b on b.impn = TRIM(a.displayproductnumber)  
       inner join KBM400MFG.FKLOCMST c on c.lmpn = TRIM(a.displayproductnumber)  
         where imsts not in ('D','4') and imco in('8') and lmco in('8') AND
          NOT EXISTS (SELECT * FROM QTEMP.ITEMS D
              WHERE  ITEMID = A.ITEMID AND  IMPRFG = B.IMPRFG AND DISPLAYPRODUCTNUMBER = A.DISPLAYPRODUCTNUMBER AND INVENTDIM_CONFIGID = A.INVENTDIM_CONFIGID AND IMSTS = B.IMSTS AND LMCODE = C.LMCODE AND RTRCODE = B.IMRTRN AND LMPN = C.LMPN)
);
 
select * from qtemp.items where displayproductnumber like '%00-047-89%' ; 


UPDATE qtemp.items a
SET locationdescription =   
                           			  (SELECT MIN(SUBSTR(AX_DESC,1,5)) 
                                      FROM   VD_GPL.AX_SHORT_DESC
                                        INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.imco = lmco);
                                      
UPDATE qtemp.items a
SET locationdescription =                                       
                                      trim(LOCATIONDESCRIPTION) CONCAT ' : ' CONCAT
                                      (SELECT SUBSTR(lmsdes,1,10) 
                                      FROM   kbm400mfg.fklocmst 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = lmcode and a.imco = lmco);
                                 
                                      
UPDATE qtemp.items a
SET locationdescription = 

					CASE
						WHEN        (SELECT MIN(locationdescription) FROM QTEMP.ITEMS  B
						                                  INNER JOIN KBM400MFG.FKLOCMST C ON A.IMCO = C.LMCO AND C.LMPN = trim(A.displayproductnumber)
						                                  WHERE A.LMCODE = C.LMCODE AND A.IMCO = C.LMCO AND C.LMPN = trim(A.displayproductnumber))  = ' '
	            		THEN        (SELECT MIN(SUBSTR(LMCODE,1,5)) FROM KBM400mfg.FKLOCMST D 
	            		                                      WHERE a.lmcode = D.lmcode and D.lmpn = trim(A.displayproductnumber) and D.lmco = a.imco)
				    END;

Open in new window

What I am trying to achieve here is this: if the first two updates to the Locationdescription field in the temporary file qtemp.items
does not populate the field, then populate the field with the fields LMCODE & LMSDES. Now I realize that there is not code for the later this is because I have been trying to get the LMCODE to populate first. I have been trying to problem solve, one small part at a time.

I am running this in DB2 ISERIES SQL and it is very CPU intensive on the AS400, it causes it to run at 98%. It runs a long time and sucks the CPU up like mad. I have not allowed it to go to completion yet since it is such a heavy hitter that it starts to impact the business workload and of course that it not good at all. I cannot run this on our development box as the data is woefully out of date and the AX* files do not exist there, and the is a very small processor there, but that is another matter altogether.

Can someone help me to understand what I need to fix in this code (Specifically the last UPDATE) that can make the code run efficently and not run the CPU usage so high AND achive popuating the code (LMCODE) and desccription (LMSDES) when the first two UPDATE fail to populate the LocationDescription field?

I am attaching a input table showing two records that would be processing in this code and the results I am getting prior to the third update statement.

My thanks in advance for help to resolve these issues.
input.xlsx
result.xls

Leogal

ASKER

More detail, Job log details showing my join is not correct :

                        Additional Message Information                        
                                                                              
Message ID . . . . . . :   SQL0199       Severity . . . . . . . :   30        
Message type . . . . . :   Diagnostic                                         
Date sent  . . . . . . :   04/17/15      Time sent  . . . . . . :   10:20:22  
                                                                              
Message . . . . :   Keyword JOIN not expected. Valid tokens: , FROM INTO.     
Cause . . . . . :   The keyword JOIN was not expected here.  A syntax error   
  was detected at keyword JOIN.  The partial list of valid tokens is , FROM   
  INTO. This list assumes that the statement is correct up to the unexpected  
  keyword.  The error may be earlier in the statement but the syntax of the   
  statement seems to be valid up to this point.                               
Recovery  . . . :   Examine the SQL statement in the area of the specified    
  keyword.  A colon or SQL delimiter may be missing. SQL requires reserved    
  words to be delimited when they are used as a name. Correct the SQL         
  statement and try the request again.

Open in new window

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leogal

ASKER

@DaveSlash, I am trying to use the Visual Explain tool. It looks like it is good, but I cannot afford to spend to much time in it at this time, I am on a tight deadline to deliver. I modified the code for the third update to this:

UPDATE qtemp.items a
SET locationdescription = 

Case 	
when   (SELECT locationdescription WHERE LOCATIONDESCRIPTION =  ' ')
then     (select lmcode concat ' : ' concat lmsdes 
 from kbm400mfg.fklocmst b
 where b.lmco = a.imco and b.lmcode = a.lmcode and b.lmpn = trim(a.displayproductnumber) )
END;

Open in new window

and received this error:

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token LOCATIONDESCRIPTION was not valid. Valid tokens: , FROM INTO. Cause . . . . . :   A syntax error was detected at token LOCATIONDESCRIPTION.  Token LOCATIONDESCRIPTION is not a valid token.  A partial list of valid tokens is , FROM INTO.  This list assumes that the statement is correct up to the token.  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery  . . . :   Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token LOCATIONDESCRIPTION. Correct the statement.  The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

Open in new window

Although the code breezed through when I ran it through Visual Explain and Run it failed here and did not return a Visual Explain diagram.

SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_2484401

I've never used a SELECT statement inside of a WHEN clause of a CASE statement. I wonder if that's even valid. I'll have to test more.

Overall, though, you'll make your own life MUCH easier if you format your SQL in a somewhat "intuitive" and legible way ... with "proper" indenting. That makes your code much easier to understand (and debug later).

HTH,
DaveSlash

Member_2_2484401

Also, since I don't know the layout of your database, I am somewhat speculating here, but I suspect the performance problem has to do with a lack of useful indexes. That's one of those things Visual Explain can point out.

HTH,
DaveSlash

Member_2_2484401

I could be wrong here, but i think whatever follows the WHEN clause has to evaluate to true or false,

I don't think this evaluates to true or false:

(SELECT locationdescription WHERE LOCATIONDESCRIPTION = ' ')

Leogal

ASKER

@Kdo (Kent) If I am reading your statements correctly, I need to use one case statement with perhaps two WHEN statements? I did so and combined the 3 update statements into one case statement, that code looks like this:

UPDATE qtemp.items a
SET locationdescription =  
 
 CASE
                              WHEN
                                      (SELECT MIN(SUBSTR(AX_DESC,1,5)) 
                                      FROM   VD_GPL.AX_SHORT_DESC
                                        INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.imco = lmco) > ' '  
                                  THEN
                                      (SELECT MIN(SUBSTR(AX_DESC,1,5)) 
                                      FROM   VD_GPL.AX_SHORT_DESC
                                        INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.imco = lmco) 
                                          ||' : '||
                                     (SELECT SUBSTR(lmsdes,1,10) 
                                      FROM   kbm400mfg.fklocmst 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = lmcode and a.imco = lmco)
                               WHEN
                                       (SELECT MIN(SUBSTR(AX_DESC,1,5)) 
                                      FROM   VD_GPL.AX_SHORT_DESC
                                        INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.imco = lmco) =' '
                                 THEN 
                                 	  (SELECT lmcode
                                      FROM kbm400mfg.fklocmst b  
                                      WHERE b.lmpn = trim(a.displayproductnumber) 
                                        AND b.lmcode = a.lmcode)
                                         ||' : '||
                                     (SELECT SUBSTR(lmsdes,1,10) 
                                      FROM   kbm400mfg.fklocmst 
                                      WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = lmcode and a.imco = lmco)                                     
 
					  END;       
					  
select * from qtemp.items order by ItemId;
select * from qtemp.items where itemid = '00-047-89';

Open in new window

Much better I think , except that the results are still the same as the attachment..

Kent Olsen

DB2 supports 2 forms of the CASE statement.

CASE (identifier)
WHEN value1 THEN
WHEN value2 THEN
...
ELSE
END

and

CASE
WHEN (logical condition1) THEN
WHEN (logical condition2) THEN
...
ELSE
END

The SQL that you posted doesn't conform to either of those so there's work to do there.

Can you explain what the selection/update criteria is?

Leogal

ASKER

@kdo Please accept my apologies for my inept attempt.

I am trying to achieve the following:

CASE
WHEN AX_DESC FOUND (NOT = ' '
THEN POPULATE THE LOCATIONDESCRIPTION FIELD WITH :
AX_DESC (A CODE LIKE 510ST) CONCAT : CONCAT LOCATION DESCRIPTION (LMSDES)
RESULT (510ST : WASA12-C-8)

WHEN AX_DESC NOT FOUND ( = ' ' )
THEN POPULATE THE LOCATIONDESCRIPTION FIELD WITH :
LMCODE (A CODE LIKE 510ST) CONCAT : CONCAT LOCATION DESCRIPTION (LMSDES)
RESULT (RM0SMI : WAS-13-C)

ELSE LMCODE (510SX)

END

Leogal

ASKER

here is what the AX_DESC file looks like :

(KBM_DESC) (AX_DESC)
KBM Short AX Short
Description Description
ZINC1 ZINC1
COL/X XROADS
XROAD XROAD
WIL/X XROAD
WIL WIL
WILL WIL
WILEC WIL
WL1LJ WIL
WHSE WHSE

both fields are char(20) bu the actual data is only a maximum of Char(5).
if there is not a hit on the KBM_DESC then I must pull the code & description from FKLOCMST

Kent Olsen

How many rows are in the items table?

How many rows are in fklocmst?

Leogal

ASKER

Rows in Items, 72,376
Rows in Fklockmst, 293,446

Kent Olsen

If the tables are properly indexed, updating only 72,000 rows should be quick.

The SQL suggests that AX_DESC and LMCODE are both in the items table and that you're first checking AX_DESC and then LMCODE. Is that correct?

The SQL also suggests that you're checking for blank to determine found/not found. Are you really checking for blank or will the value be NULL? Also, is it guaranteed that if the value is non-blank that it will occur in FKLOCMST?

Leogal

ASKER

@kdo, with this new code it runs very fast. I am checking the AX_DESC first if it is blank then I go and select the LMCODE. That is correct. I am also using blank /non blank for found or not found. the items will always be in FKLOCMST but the code translation may not exist in the AX_SHORT_DESC file.

UPDATE qtemp.items a
SET locationdescription =  
 
 CASE
 WHEN
             (SELECT MIN(trim(AX_DESC)) 
              FROM   VD_GPL.AX_SHORT_DESC
              INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
               WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.lmco = lmco) > ' '  
            
THEN
             (SELECT MIN(trim(AX_DESC)) 
              FROM   VD_GPL.AX_SHORT_DESC
              INNER JOIN KBM400MFG.FKLOCMST ON a.lmcode = KBM_DESC 
               WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = KBM_DESC and a.lmco = lmco) 
               ||' : '||
              (SELECT SUBSTR(lmsdes,1,10) 
              FROM   kbm400mfg.fklocmst 
              WHERE  lmpn = trim(a.displayproductnumber) and a.lmcode = lmcode and a.lmco = lmco)
 
ELSE
               (SELECT trim(lmcode) FROM kbm400mfg.fklocmst b  
                WHERE b.lmpn = trim(a.displayproductnumber) and a.lmco = b.lmco and b.lmcode = a.lmcode)
                ||' : '||
                 (SELECT SUBSTR(lmsdes,1,10) FROM   kbm400mfg.fklocmst b
                    WHERE  lmpn = trim(a.displayproductnumber) and a.lmco = b.lmco and a.lmcode = b.lmcode)                                     
 
END;

Open in new window

I am doubling checking the results file. I am now finally getting both the items entries populated with the appropriate code & description on the sample. now to check the rest ....