Hi Simon,
The common wisdom is to always access the TABLE from SQL. The SQL Engine is much smarter than I can ever be, at least at Data Access :)
The Programmatic selection should remain in the DSPFIL Source code. The SQL Database engine will see the SQL Request, and say to itself that looks like this INDEX I have, I will use that to access the data.
In fact, you will find that for SQLRPG you cannot SELECT * FROM INDEX. In fact, you can't SELECT * from an INDEX period. It is sometimes Ok to SELECT * FROM a VIEW, because the VIEW may have User Defined functions that create fields, and so on.
But, for the 2E SQL Data Access, I wouldn't do much with it at all. Leave it be and let the system handle it. Now, if it was selecting the records out after it read them, I would say that was a problem, but because it uses a WHERE clause, all will be good and the INDEX will (should) be used.
Now, for RPG RLA (Record Level Access), I would use the INDEX directly.
I was playing around with the SQL Generator, and I see that the UUAFREL2 is generated as a VIEW. Then an INDEX is created, UUAFREL2I.
In my world, UUAFREL2 would be the INDEX code.
Here is what my INDEX Code looks like for the RTV Index over my Customer file (CCADREP).
CREATE UNIQUE INDEX CRPU551GEN/CCADREL0
ON CRPU551GEN/CCADREP ( CUST_NUMBER ASC )
RCDFMT @ADREAR ;
LABEL ON INDEX CRPU551GEN/CCADREL0
IS 'Customer Retrieval index' ;
Now, my DDL for the CCADREP TABLE looks like this.
CREATE TABLE CRPU551GEN/CCADREP (
Cust_Number FOR COLUMN
ADADCD CHAR(3) CCSID 37 NOT NULL DEFAULT '' ,
Cust_Name FOR COLUMN
ADABTX CHAR(25) CCSID 937 NOT NULL DEFAULT '' ,
Cust_Street FOR COLUMN
ADACTX CHAR(20) CCSID 937 NOT NULL DEFAULT '' ,
Cust_Extra_Line FOR COLUMN
ADADTX CHAR(25) CCSID 937 NOT NULL DEFAULT '' ,
Cust_City FOR COLUMN
ADAETX CHAR(17) CCSID 937 NOT NULL DEFAULT '' ,
Cust_State FOR COLUMN
ADAFTX CHAR(2) CCSID 37 NOT NULL DEFAULT '' ,
Cust_Country FOR COLUMN
ADQMCD CHAR(3) CCSID 37 NOT NULL DEFAULT '' ,
Cust_Zipcode FOR COLUMN
ADAGTX CHAR(9) CCSID 37 NOT NULL DEFAULT '' ,
DUNS_ID FOR COLUMN
ADDBTX CHAR(9) CCSID 37 NOT NULL DEFAULT '' )
RCDFMT @ADREAT ;
LABEL ON TABLE CRPU551GEN/CCADREP
IS 'Customer Physical file' ;
LABEL ON COLUMN CRPU551GEN/CCADREP
( ADADCD IS 'Cust Number' ,
ADABTX IS 'Cust Name' ,
ADACTX IS 'Cust Street' ,
ADADTX IS 'Cust Extra Line' ,
ADAETX IS 'Cust City' ,
ADAFTX IS 'Cust State' ,
ADQMCD IS 'Cust Country' ,
ADAGTX IS 'Cust Zipcode' ,
ADDBTX IS 'DUNS ID' ) ;
LABEL ON COLUMN CRPU551GEN/CCADREP
( ADADCD TEXT IS 'Cust Number' ,
ADABTX TEXT IS 'Cust Name' ,
ADACTX TEXT IS 'Cust Street' ,
ADADTX TEXT IS 'Cust Extra Line' ,
ADAETX TEXT IS 'Cust City' ,
ADAFTX TEXT IS 'Cust State' ,
ADQMCD TEXT IS 'Cust Country' ,
ADAGTX TEXT IS 'Cust Zipcode' ,
ADDBTX TEXT IS 'DUNS ID' ) ;
So, I have a little more than you do, as I think the UUAFREP is probably defining the long names directly.
I need the short internal RPG like names so that this will all work with RPG RLA. The DDL for the file I show works with the 2E Generated RPG Code, as if it was a DDS LF.
I get this for my test file in my test model
EXEC SQL
CREATE TABLE CBTEMPSQL.CUSTOMER (
CUSTOMER_CODE CHAR(6) NOT NULL WITH DEFAULT
,CUSTOMER_NAME CHAR(25) NOT NULL WITH DEFAULT
,CUSTOMER_ADDRESS CHAR(25) NOT NULL WITH DEFAULT
,CUSTOMER_CODE______001045 CHAR(6) NOT NULL WITH DEFAULT
,ALLOW_IN_COMPUTE_EXPRESSI CHAR(1) NOT NULL WITH DEFAULT
,UNUSED_FIELD CHAR(25) NOT NULL WITH DEFAULT
,LONG_KEY CHAR(500) NOT NULL WITH DEFAULT
)
END-EXEC
EXEC SQL
COMMENT ON TABLE CBTEMPSQL.CUSTOMER IS
'Customer Physical file'
END-EXEC
EXEC SQL
COMMENT ON CBTEMPSQL.CUSTOMER (
CUSTOMER_CODE IS 'Customer Code'
,CUSTOMER_NAME IS 'Customer Name'
,CUSTOMER_ADDRESS IS 'Customer Address'
,CUSTOMER_CODE______001045 IS 'Customer Code'
,ALLOW_IN_COMPUTE_EXPRESSI IS 'Allow in Compute Expression'
,UNUSED_FIELD IS 'Unused Field'
,LONG_KEY IS 'Long Key'
)
END-EXEC
EXEC SQL
LABEL ON TABLE CBTEMPSQL.CUSTOMER IS
'Customer Physical file'
END-EXEC
EXEC SQL
LABEL ON CBTEMPSQL.CUSTOMER (
CUSTOMER_CODE IS 'Customer Code'
,CUSTOMER_NAME IS 'Customer Name'
,CUSTOMER_ADDRESS IS 'Customer Address'
,CUSTOMER_CODE______001045 IS 'Customer Code'
,ALLOW_IN_COMPUTE_EXPRESSI IS 'Allow in Expression'
,UNUSED_FIELD IS 'Unused Field'
,LONG_KEY IS 'Long Key'
)
END-EXEC
EXEC SQL
LABEL ON CBTEMPSQL.CUSTOMER (
CUSTOMER_CODE TEXT IS 'Customer Code'
,CUSTOMER_NAME TEXT IS 'Customer Name'
,CUSTOMER_ADDRESS TEXT IS 'Customer Address'
,CUSTOMER_CODE______001045 TEXT IS 'Customer Code'
,ALLOW_IN_COMPUTE_EXPRESSI TEXT IS 'Allow in Expression'
,UNUSED_FIELD TEXT IS 'Unused Field'
,LONG_KEY TEXT IS 'Long Key'
)
END-EXEC
The slight difference is that I have the FOR COLUMN clause to define the Short Names. The TABLE is also called CUSTOMER, and it should (would be in my perfect world) be called UUACREP. That may be another setting somewhere, I haven't used SQL in 2E much...
I hope this helps you out. It's a little bit more tricky than it first looked, but hopefully not insurmountable!
Thanks,
Crispin.