Topic
  • 3 replies
  • Latest Post - ‏2014-03-13T20:35:39Z by JillD
JillD
JillD
11 Posts

Pinned topic FieldProc Drop issue

‏2014-03-12T21:57:55Z |

My team is working on an in-house encryption solution using FieldProcs.  We are storing all of our customer sensitive data on a single database and encrypting each field using a Fieldproc exit routine.

I am attempting to change the program name on one of the fields and think that I must first drop the fieldproc and then re-add with the new program name.  In dropping, I am getting an error.  My sql code looks like this:

  ALTER TABLE mylib/mytable ALTER COLUMN field2 DROP FIELDPROC

I am getting this error:

Field procedure on column field1 returned SQLSTATE 09501.

 

Note that the error is not on the field that I am trying to drop the  field proc on but one that comes earlier in the file.  They have different exit routine names.

Is my code wrong?  Can you drop a single fieldproc.  Is there a better way to change the name of the exit routine?

 

Thank you for any help you can provide!

 

Updated on 2014-03-12T21:59:04Z at 2014-03-12T21:59:04Z by JillD
  • krmilligan
    krmilligan
    450 Posts
    ACCEPTED ANSWER

    Re: FieldProc Drop issue

    ‏2014-03-13T13:45:07Z  
    • JillD
    • ‏2014-03-13T13:40:39Z

    I believe I  have resolved my immediate  issue though I do not know why exactly.  Field1 has an exit routine that checks for authority to determine whether the access would  return a masked value or a clear text value.  At the time that I submitted the Alter Table to drop the fieldproc my userid did not have authority.  Changing my userid to have full authority allowed the drop to process.

    HOWEVER, I still would like to understand why Field1 was involved at all in the dropping of the Fieldproc on Field 2.

    I saw this message while the Fieldproc drop was processing:

    Copying member or label myfile in Q_AT000000 in mylibrary.

    Is the entire file being copied????

    Yes, the drop of a FieldProc program forces all the existing data to be read, so that the dropped FieldProc column can be decoded back to the original value.

  • JillD
    JillD
    11 Posts

    Re: FieldProc Drop issue

    ‏2014-03-13T13:40:39Z  

    I believe I  have resolved my immediate  issue though I do not know why exactly.  Field1 has an exit routine that checks for authority to determine whether the access would  return a masked value or a clear text value.  At the time that I submitted the Alter Table to drop the fieldproc my userid did not have authority.  Changing my userid to have full authority allowed the drop to process.

    HOWEVER, I still would like to understand why Field1 was involved at all in the dropping of the Fieldproc on Field 2.

    I saw this message while the Fieldproc drop was processing:

    Copying member or label myfile in Q_AT000000 in mylibrary.

    Is the entire file being copied????

  • krmilligan
    krmilligan
    450 Posts

    Re: FieldProc Drop issue

    ‏2014-03-13T13:45:07Z  
    • JillD
    • ‏2014-03-13T13:40:39Z

    I believe I  have resolved my immediate  issue though I do not know why exactly.  Field1 has an exit routine that checks for authority to determine whether the access would  return a masked value or a clear text value.  At the time that I submitted the Alter Table to drop the fieldproc my userid did not have authority.  Changing my userid to have full authority allowed the drop to process.

    HOWEVER, I still would like to understand why Field1 was involved at all in the dropping of the Fieldproc on Field 2.

    I saw this message while the Fieldproc drop was processing:

    Copying member or label myfile in Q_AT000000 in mylibrary.

    Is the entire file being copied????

    Yes, the drop of a FieldProc program forces all the existing data to be read, so that the dropped FieldProc column can be decoded back to the original value.

  • JillD
    JillD
    11 Posts

    Re: FieldProc Drop issue

    ‏2014-03-13T20:35:39Z  

    Yes, the drop of a FieldProc program forces all the existing data to be read, so that the dropped FieldProc column can be decoded back to the original value.

    Thanks Krmilligan!

    Just so I can be sure I understand...  It sounds like you are saying that  the drop of one fieldProc on one field forces all the other fields (with other fieldprocs) to be read.  I can understand why the field that has the fieldproc dropped would need to be read and decoded but not all the other fields.

    That makes perfect sense then why I got that error but disappointing to learn that is what is happening.

     

    Thanks!