There seems to be quite a bit of confusion as to whether or not you can create simple select/omit LF's with a key by using DDL.  You can.  This has been around for quite some time.

Many legacy ERP systems have select/omit logical files which look like this:

R IPI100IM                  PFILE(IIM       )        
  IID            2A         TEXT('Record ID (IM/IZ)')
                            COLHDG('Rec' +           
                                   'ID' +            
                                   'IM/IZ')          
  IPROD         35O         TEXT('Item Number')      
                            COLHDG('Item' +          
                                   'Number' )        

...

K IPROD                                  
S IID                       COMP(EQ 'IM')

Basically, create a key on the item master file by the item number and select only active records.

To do this with DDL you can use the following:

CREATE INDEX ROB.IIML01_DDL
    ON ERPLXF.IIM
    (IPROD ASC)
    WHERE IID = 'IM'
    RCDFMT IPI100IM
    ADD IID, IPROD ...
;

Forget about trying to generate this by using generate_sql or even the underlying API, Generate Data Definition Language (QSQGNDDL) API - IBM Documentation

There seems to be quite a bit of confusion as to whether or not you can create simple select/omit LF's with a key by using DDL.  You can.  This has been around for quite some time.

Many legacy ERP systems have select/omit logical files which look like this:

R IPI100IM                  PFILE(IIM       )        
  IID            2A         TEXT('Record ID (IM/IZ)')
                            COLHDG('Rec' +           
                                   'ID' +            
                                   'IM/IZ')          
  IPROD         35O         TEXT('Item Number')      
                            COLHDG('Item' +          
                                   'Number' )        

...

K IPROD                                  
S IID                       COMP(EQ 'IM')

Basically, create a key on the item master file by the item number and select only active records.

To do this with DDL you can use the following:

CREATE INDEX ROB.IIML01_DDL
    ON ERPLXF.IIM
    (IPROD ASC)
    WHERE IID = 'IM'
    RCDFMT IPI100IM
    ADD IID, IPROD ...
;

Forget about trying to generate this by using generate_sql or even the underlying API, Generate Data Definition Language (QSQGNDDL) API - IBM Documentation

One of the cardinal rules of sql is that you always SELECT FROM a table.

However if the object is a DDS LF you can select from that.  If you generate that same object using DDL it then knows it is an index and will not allow you to do a select from it.

One caveat before you undergo a mass conversion.

There seems to be quite a bit of confusion as to whether or not you can create simple select/omit LF's with a key by using DDL.  You can.  This has been around for quite some time.

Many legacy ERP systems have select/omit logical files which look like this:

R IPI100IM                  PFILE(IIM       )        
  IID            2A         TEXT('Record ID (IM/IZ)')
                            COLHDG('Rec' +           
                                   'ID' +            
                                   'IM/IZ')          
  IPROD         35O         TEXT('Item Number')      
                            COLHDG('Item' +          
                                   'Number' )        

...

K IPROD                                  
S IID                       COMP(EQ 'IM')

Basically, create a key on the item master file by the item number and select only active records.

To do this with DDL you can use the following:

CREATE INDEX ROB.IIML01_DDL
    ON ERPLXF.IIM
    (IPROD ASC)
    WHERE IID = 'IM'
    RCDFMT IPI100IM
    ADD IID, IPROD ...
;

Forget about trying to generate this by using generate_sql or even the underlying API, Generate Data Definition Language (QSQGNDDL) API - IBM Documentation

There seems to be quite a bit of confusion as to whether or not you can create simple select/omit LF's with a key by using DDL.  You can.  This has been around for quite some time.

Many legacy ERP systems have select/omit logical files which look like this:

R IPI100IM                  PFILE(IIM       )        
  IID            2A         TEXT('Record ID (IM/IZ)')
                            COLHDG('Rec' +           
                                   'ID' +            
                                   'IM/IZ')          
  IPROD         35O         TEXT('Item Number')      
                            COLHDG('Item' +          
                                   'Number' )        

...

K IPROD                                  
S IID                       COMP(EQ 'IM')

Basically, create a key on the item master file by the item number and select only active records.

To do this with DDL you can use the following:

CREATE INDEX ROB.IIML01_DDL
    ON ERPLXF.IIM
    (IPROD ASC)
    WHERE IID = 'IM'
    RCDFMT IPI100IM
    ADD IID, IPROD ...
;

Forget about trying to generate this by using generate_sql or even the underlying API, Generate Data Definition Language (QSQGNDDL) API - IBM Documentation

There seems to be quite a bit of confusion as to whether or not you can create simple select/omit LF's with a key by using DDL.  You can.  This has been around for quite some time.

Many legacy ERP systems have select/omit logical files which look like this:

R IPI100IM                  PFILE(IIM       )        
  IID            2A         TEXT('Record ID (IM/IZ)')
                            COLHDG('Rec' +           
                                   'ID' +            
                                   'IM/IZ')          
  IPROD         35O         TEXT('Item Number')      
                            COLHDG('Item' +          
                                   'Number' )        

...

K IPROD                                  
S IID                       COMP(EQ 'IM')

Basically, create a key on the item master file by the item number and select only active records.

To do this with DDL you can use the following:

CREATE INDEX ROB.IIML01_DDL
    ON ERPLXF.IIM
    (IPROD ASC)
    WHERE IID = 'IM'
    RCDFMT IPI100IM
    ADD IID, IPROD ...
;

Forget about trying to generate this by using generate_sql or even the underlying API, Generate Data Definition Language (QSQGNDDL) API - IBM Documentation

Not sure what's your Problem with the WIZARD in ACS or the GENERATE_SQL stored procedure. For me it works perfectly.

There are a few things that are not supported in an index.

Here an article I wrote somtimes ago for IBM:

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/

There seems to be quite a bit of confusion as to whether or not you can create simple select/omit LF's with a key by using DDL.  You can.  This has been around for quite some time.

Many legacy ERP systems have select/omit logical files which look like this:

R IPI100IM                  PFILE(IIM       )        
  IID            2A         TEXT('Record ID (IM/IZ)')
                            COLHDG('Rec' +           
                                   'ID' +            
                                   'IM/IZ')          
  IPROD         35O         TEXT('Item Number')      
                            COLHDG('Item' +          
                                   'Number' )        

...

K IPROD                                  
S IID                       COMP(EQ 'IM')

Basically, create a key on the item master file by the item number and select only active records.

To do this with DDL you can use the following:

CREATE INDEX ROB.IIML01_DDL
    ON ERPLXF.IIM
    (IPROD ASC)
    WHERE IID = 'IM'
    RCDFMT IPI100IM
    ADD IID, IPROD ...
;

Forget about trying to generate this by using generate_sql or even the underlying API, Generate Data Definition Language (QSQGNDDL) API - IBM Documentation

it doesn't return the same number of columns as were on the CREATE statement

Not sure what's your Problem with the WIZARD in ACS or the GENERATE_SQL stored procedure. For me it works perfectly.

There are a few things that are not supported in an index.

Here an article I wrote somtimes ago for IBM:

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/

There seems to be quite a bit of confusion as to whether or not you can create simple select/omit LF's with a key by using DDL.  You can.  This has been around for quite some time.

Many legacy ERP systems have select/omit logical files which look like this:

R IPI100IM                  PFILE(IIM       )        
  IID            2A         TEXT('Record ID (IM/IZ)')
                            COLHDG('Rec' +           
                                   'ID' +            
                                   'IM/IZ')          
  IPROD         35O         TEXT('Item Number')      
                            COLHDG('Item' +          
                                   'Number' )        

...

K IPROD                                  
S IID                       COMP(EQ 'IM')

Basically, create a key on the item master file by the item number and select only active records.

To do this with DDL you can use the following:

CREATE INDEX ROB.IIML01_DDL
    ON ERPLXF.IIM
    (IPROD ASC)
    WHERE IID = 'IM'
    RCDFMT IPI100IM
    ADD IID, IPROD ...
;

Forget about trying to generate this by using generate_sql or even the underlying API, Generate Data Definition Language (QSQGNDDL) API - IBM Documentation

I so, you should report a problem to IBM

it doesn't return the same number of columns as were on the CREATE statement

Not sure what's your Problem with the WIZARD in ACS or the GENERATE_SQL stored procedure. For me it works perfectly.

There are a few things that are not supported in an index.

Here an article I wrote somtimes ago for IBM:

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/

There seems to be quite a bit of confusion as to whether or not you can create simple select/omit LF's with a key by using DDL.  You can.  This has been around for quite some time.

Many legacy ERP systems have select/omit logical files which look like this:

R IPI100IM                  PFILE(IIM       )        
  IID            2A         TEXT('Record ID (IM/IZ)')
                            COLHDG('Rec' +           
                                   'ID' +            
                                   'IM/IZ')          
  IPROD         35O         TEXT('Item Number')      
                            COLHDG('Item' +          
                                   'Number' )        

...

K IPROD                                  
S IID                       COMP(EQ 'IM')

Basically, create a key on the item master file by the item number and select only active records.

To do this with DDL you can use the following:

CREATE INDEX ROB.IIML01_DDL
    ON ERPLXF.IIM
    (IPROD ASC)
    WHERE IID = 'IM'
    RCDFMT IPI100IM
    ADD IID, IPROD ...
;

Forget about trying to generate this by using generate_sql or even the underlying API, Generate Data Definition Language (QSQGNDDL) API - IBM Documentation

I did open a case.

Here was the sample sparse index I created:

CREATE INDEX ROB.IIML01
ON rob.IIM
(IPROD ASC)
WHERE IID = 'IM'
RCDFMT IPI100IM
ADD IID, IPROD
;

Here is what the generate returned:

CREATE INDEX ROB/IIML01
ON ROB/IIM
( IPROD ASC )
WHERE IID = 'IM'
 RCDFMT IPI100IM ADD IID ;

Notice only one column on the ADD?  You only see IID and not IPROD also.

However, it was determined that DSPFFD did show both columns.  IPROD was added implicitly to the list of columns by the (IPROD ASC)

This makes it working as designed.  The only problem is, that it will not replace a bulk of the indexes used by this ERP system.  Which is why I think IBM really came out with this form of sparse index in the first place.  The reason it will not replace them is because it places the columns in the wrong order by putting the index column(s) first and not in the order of the ADD clause.

I so, you should report a problem to IBM

it doesn't return the same number of columns as were on the CREATE statement

Not sure what's your Problem with the WIZARD in ACS or the GENERATE_SQL stored procedure. For me it works perfectly.

There are a few things that are not supported in an index.

Here an article I wrote somtimes ago for IBM:

https://developer.ibm.com/articles/i-sql-indexs-and-native-io/

There seems to be quite a bit of confusion as to whether or not you can create simple select/omit LF's with a key by using DDL.  You can.  This has been around for quite some time.

Many legacy ERP systems have select/omit logical files which look like this:

R IPI100IM                  PFILE(IIM       )        
  IID            2A         TEXT('Record ID (IM/IZ)')
                            COLHDG('Rec' +           
                                   'ID' +            
                                   'IM/IZ')          
  IPROD         35O         TEXT('Item Number')      
                            COLHDG('Item' +          
                                   'Number' )        

...

K IPROD                                  
S IID                       COMP(EQ 'IM')

Basically, create a key on the item master file by the item number and select only active records.

To do this with DDL you can use the following:

CREATE INDEX ROB.IIML01_DDL
    ON ERPLXF.IIM
    (IPROD ASC)
    WHERE IID = 'IM'
    RCDFMT IPI100IM
    ADD IID, IPROD ...
;

Forget about trying to generate this by using generate_sql or even the underlying API, Generate Data Definition Language (QSQGNDDL) API - IBM Documentation