Help on SQL Server 7 to AS/400 conversion on V4R4

[KirkG@xxxxxxxxxxxxxx]

I'm in the process of converting a MS SQL SERVER 7 database to the AS/400 
for a proof of concept project. I found the web pages at IBM's Tech Studio 
and they have been of great help for a start.

I'm down to a few errors, but SQL is not a second lang. for me I need some 
help. Per the web pages I used  SQL Server to create a TABLE script and 
then started modifying it so it will run on the AS/400.  The script does a 
bunch on CREATE TABLE cmds(which I have most of the errors out of) and 
then proceeds to use ALTER TABLE to ADD CONSTRAINTs which all die at this 
point. (BTW I'm running the RUNSQLSTM in *SYN to only check syntax) and 
example of a partial ALTER TABLE looks like:

 ALTER TABLE COPYLMS.ACTIVITYTYPE
        ADD CONSTRAINT DF_ACTIVITYTYPE_OVERRIDECREDITCOMPANY_ID 
        WITH DEFAULT '0' FOR OVERRIDECREDITCOMPANY_ID,
 
        ADD CONSTRAINT DF_ACTIVITYTYPE_OVERRIDECREDITLOCATION_ID 
        WITH DEFAULT '666' FOR OVERRIDECREDITLOCATION_ID,

        ADD CONSTRAINT PK__ACTIVITYTYPE__69C6B1F5 PRIMARY KEY
        (
                ACTIVITYTYPE_ID
        ) WITH  FILLFACTOR = 90 
;

The AS/400 is complaining about the WITH DEFAULT with error msg

SQL0199  30    1486  Position 2 Keyword WITH not expected. Valid tokens: 
                     CHECK UNIQUE FOREIGN PRIMARY. 

According to the book I have...
"DEFAULT is a constraint that determines which value is inserted into the 
column if no value is specified for the column on an insert"

How do I do this on the AS/400??


The AS/400 is complaining about the WITH FILLFACTOR with error msg

SQL0199  30    1525  Position 4 Keyword WITH not expected. Valid tokens: 
ADD DROP ALTER. 

I don't see a AS/400 equiv of FILLFACTOR . According to book I have...
"FILLFACTOR describes how much data is on  an index page. Available values 
are 1 to 100 with a default of 0, indicated in percentage. the lower the 
fillfactor, the more space is available between index entries on the page, 
which leaves more space to insert new rows on the page without forcing SQL 
Server to allocate more space for the new rows."

Do I even care on the AS/400 about the FILLFACTOR parm ?


Many Thanks in advance
 
---------------------------------
Kirk Goins
IBM Certified AS/400 Technical Solutions
Pacific Information Systems - An IBM Premier Business Partner
503-290-2104
kirkg@pacinfosys.com
---------------------------------

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---