Some DB2 for OS/400 SQL syntax questions 
Author Message
 Some DB2 for OS/400 SQL syntax questions

An ISV who is MS SQL Server expert posted some questions to me for help.
They R developing SQL for one of my AS/400 customer. Here R the
questions:

1) AS/400 cannot handle this:
UPDATE A Set A.Zone = B.Zone From F_Accnts A, F_Area B Where A.Area =
B.Area

They used this instead but found it very slow:
 UPDATE F_Accnts Set F_Accnts.Zone = (Select F_Area.Zone from F_Area
 where F_Area.Area_ID = F_Accnts.Area_ID)

Any suggestion on how to improve the run-time performance other than
using Visual Explain to analyse it ?

2) AS/400 cannot handle this:
 Select * from F_Accnts where right(ID_Type,2) = '19'

Can SUBSTRING provide equivalent result ?  Does it work with VARCHAR
field ?

3) AS/400 cannot handle this:
 Create View Test as Select * from zTime1 Union Select * from zTime2

What can be an equivalent statement that AS/400 supports ?

4) AS/400's Date data type only support the year from 1940 to 2039. I
suggest to use TIMESTAMP instead because it run from year 1900 to 9999.
Is this a proper suggestion ?

5) How can AS/400 provide an equivalent of the following date
manipulation funtions of MS SQL:
 Select DatePart(mm,Date_Tx1) from Test  and
 Select Date_Tx1 - 10 from Test

I thank U in advance for any help.

================================================
Satid S.
IBM Thailand



Wed, 11 Aug 2004 17:31:41 GMT
 Some DB2 for OS/400 SQL syntax questions

Hi Satid,

Quote:
> 1) AS/400 cannot handle this:
> UPDATE A Set A.Zone = B.Zone From F_Accnts A, F_Area B Where A.Area =
> B.Area

> They used this instead but found it very slow:
>  UPDATE F_Accnts Set F_Accnts.Zone = (Select F_Area.Zone from F_Area
>  where F_Area.Area_ID = F_Accnts.Area_ID)

That is semantically not a correct transformation, you need this:
UPDATE F_Accnts Set F_Accnts.Zone = (Select F_Area.Zone from F_Area
 where F_Area.Area_ID = F_Accnts.Area_ID)
WHERE EXISTS(Select 1 from F_Area where F_Area.Area_ID = F_Accnts.Area_ID)

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada



Thu, 12 Aug 2004 22:55:07 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. desire to move data from OS 390 DB2 to AS/400 DB2 using DTS

2. DB2 Connect and DB2 OS/400

3. Access DB2 on OS/400 V3R7 from appl on V4R4

4. OS/400 DB2 UDB Compatibility issues

5. Dynamic SQL for DB2/400 from RPG and/or Rexx/400

6. OS/400 and OS/390 for dummies?

7. AS/400 DB2/400

8. AS/400 - Seeking info on using VB to access DB2/400

9. AS/400, anyone used Delphi to access DB2/400

10. Rexx/400-DB2/400 ExecSQL/SQLCode -5032

11. SQL help with SUBSTR function in OS/400 V4R5

12. Differences between SQL on DB2-MVS and DB2-OS/2 or DB2-NT


 
Powered by phpBB® Forum Software