FIELDPROCS for encryption - Reading the encoded data

[jayvaughn]

I'm currently developing an OAR handler pgm to convert RLA to SQL in order to accurately read sequential data on a file with a column that is encrypted that is also a key field.

I've tested my handler pgm and it works.
When the encrypted file is read with RLA, the data is not in sequential order after SETLL and reads are executed.
But it is in sequential order (as expected) using the OAR handler pgm that converts the RLA to SQL.

However, I am doing nothing special in my sql statements to tell sql to "act upon enrypted data".

So naturally curious to know how SQL automatically handles the true value instead of the encoded value?

[JonBoy]

How is the column encryption being done?

[jayvaughn]

via a FIELDPROC attached to the column

JonBoy - you are killing me softly smalls... i said FIELDPROC in original post am I interpreting your question wrong?

[JonBoy]

Sorry - I read the test of the post - not the heading. My bad.

OK - so in the original RLA RPG code it has always behaved this way? i.e. non-sequenced retrieval? Is the field attached to the fieldproc part/all of the key?

I have not encountered this so I'd have to do more digging, but I can only guess that for some reason SQL is using an index that does use the encrypted key and therefore it just happens to come out in sequence.

Hopefully Birgitta will see this and have some ideas.

[jayvaughn]

JonBoy, i'd like to see what Birgitta will say also. Going into FIELDPROCS for encryption purpsoses, most literature states that encrypting columns that are key fields on indexes will be an issue as RLA acts upon the encoded value and not the true value, thus skewing sequential retrieval results (setll/setgt)... however the solution is to use SQL and an OAR handler.

"Do not overlook the fact that the creation of an SQL index or a keyed logical file also results in the FieldProc program being called to encode values. What this means is that DB2 uses the encoded value of a FieldProc column when computing the internal key values for an index or logical file. As a result, the ordering of keyed values in a logical file is determined by the encoded value instead of the original data value. If an application is using a logical file with a FieldProc column as one of the key fields to performed keyed sequential access, then there's a good chance that the application will process the records in a different order."

reference..

https://www.mcpressonline.com/analytics-cognitive/db2/enable-transparent-encryption-with-db2-field-procedures