I'ver never used it, but looks like it should be

CHANGEDBY Char(10) NOT NULL DEFAULT USER

See CREATE TABLE in SQL Ref. "Specifies the value of the USER special register at the time of INSERT or UPDATE as the default value of the column."

I tried that and it wouldn't create the table--but let me verify that.

...Position 1 DEFAULT value or identity attribute not valid.

Yep it doesn't allow DEFAULT USER.

Checked V5R4, 6.1, and 7.1, and they all have it. (e.g., http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzhctabl.htm) Are you on an older release, and could this have been added along the way?

I have lots of different releases... I checked on the v5.2 system, let me go look on the v7.1 system...

 I get an error on v7.1 also.

 CHGBY Char(10) NOT NULL DEFAULT USER,

>>Column or sequence attribute is not valid.

Eureka!

When I tried to actually create one, got the same error. The help on this says when your DEFAULT is USER, the column must be CHAR or VARCHAR with a minimum length of 18, which I tried and it worked. I do not see anything in CREATE TABLE about the length 18; it is documented in the Special registers topic in Chapter 2.

You could maybe save a little storage by doing VARCHAR(18) ALLOCATE(10), but query is still going to format for 18 unless you use an expression to limit the width.

Cool...

Well I'm not happy with that--still can't do the On UPDATE thing.

So I went back to using a Trigger and this time included the ALWREPCHG(*YES) keyword and it started working:

ADDPFTRG FILE(MYLIB/GENLEDGER)                      
           TRGTIME(*BEFORE) TRGEVENT(*UPDATE)  
           PGM(MYLIB/TRG01) ALWREPCHG(*YES)

By changing the AFTER image in the trigger, I was able to insert the user profile into the original 10-character field.

Thanks for everyone's help!

Here is the source code for the TRIGGER Program in RPG IV.

Just change the file referenced by the DBFILE_T data structure and everything will fall into place. Then go into the last few lines of the code and set the fields in the AfterImage to whatever you want. Have fun! 

     H  OPTION(*NODEBUGIO:*SRCSTMT)
     H  EXTBININT(*YES)
     H  ALWNULL(*USRCTL)
      /IF DEFINED(*CRTBNDRPG)
     H DFTACTGRP(*NO)
     H ACTGRP(*CALLER)
      /ENDIF

      **************************************************************************
      **  Program: TRIGGER                                                    **
      **  Author:  R. Cozzi, Jr.                                              **
      **  Date:    Original: October 2010                                     **
      **  Purpose: To illustrate how RPG IV can be used as a DB Trigger.      **
      **           This particular example is used as an *UPDATE and *INSERT  **
      **           trigger.                                                   **
      **                                                                      **
      **  TO ADD A TRIGGER and allow updates to the AFTER IMAGE, make sure    **
      **  to include the  ALWREPCHG(*YES) parameter on the ADDPFTRG command.  **
      **                                                                      **
      **   For exampe:   ADDPFTRG FILE(MYLIB/mydatabase)                      **
      **                  TRGTIME(*BEFORE) TRGEVENT(*UPDATE)                  **
      **                  PGM(MYLIB/trigger) ALWREPCHG(*YES)                  **
      **                                                                      **
      **           This sample modifies the AFTER image of the record         **
      **           by inserting the USER PROFILE into the field named CHGBY   **
      **           This could, of course be any field in whatever file this   **
      **           trigger applies.                                           **
      **************************************************************************

     D PSDS           SDS
     D  JobNbr                        6A   Overlay(PSDS:264)
     D  JobName                      10A   Overlay(PSDS:244)
     D  JobUser                      10A   Overlay(PSDS:254)

     D usrprf          S             10A   Inz(*USER)

      *  Set this data structure template's EXTNAME file to the 
      *  file to which this Trigger Program applies.
.....D DBFile_T      E DS                  extName(GENLEDGER) Qualified
      /IF DEFINED(*V7R1M0)
     D                                     TEMPLATE
      /ENDIF

      * Pointer based data structures for the input buffer.
     D pNewData        S               *
     D pOldData        S               *

      * Map data structure for before and after images of the record
      *  in the buffer from the external de!--script--ion for the file.
     D afterImage      DS                  based(pNewData)
     D                                     LikeDS(DBFile_T)

     D beforeImage     DS                  based(pOldData)
     D                                     LikeDS(DBFile_T)

     D FILE_t          DS                  Qualified
     D  name                         10A
     D  library                      10A
     D  member                       10A
     D  lib                          10A   Overlay(library)
     D  mbr                          10A   Overlay(member)
     D  file                         10A   Overlay(name)

        //  First parameter passed to a trigger pgm is
        //  the Trigger Header structure. It contains
        //  an offset to the old/new record images.
        //  Use those offsets to assign a PTR the address
        //  of the old/new records. Then you can read that
        //  data in the trigger program as if it were passed
        //  to the program directly.
     D trigParm_T      DS                  Qualified
     D  file                               LikeDS(file_T)
     D  event                         1A
     D  eventTime                     1A
     D  commitLock                    1A
     D  filler3                       3A
     D  ccsid                        10I 0
     D  relRecNo                     10I 0
     D  filler4                       4A
     D  oldRecOffset                 10I 0
     D  oldRecLen                    10I 0
     D  oldNullOffset                10I 0
     D  oldNullLen                   10I 0
     D  newRecOffset                 10I 0
     D  newRecLen                    10I 0
     D  newNullOffset                10I 0
     D  newNullLen                   10I 0
     D  filler16                     16A

         // These CONSTANTS are sued used for trigger verification
     D triggerEvent    DS                  Qualified
     D   Insert                       1A   Inz('1')
     D   Delete                       1A   Inz('2')
     D   Update                       1A   Inz('3')

     D triggerTime     DS                  Qualified
     D   After                        1A   Inz('1')
     D   Before                       1A   Inz('2')

     D triggerCommit   DS                  Qualified
     D   None                         1A   Inz('0')
     D   Change                       1A   Inz('1')
     D   cursorStability...
     D                                1A   Inz('2')
     D   all                          1A   Inz('3')

     D MYTRIGGER       PR                  extPGM('TRIGGER')
     D  trigData                           LikeDS(trigParm_T)
     D  rtnCode                      10I 0

     D MYTRIGGER       PI
     D  trigData                           LikeDS(trigParm_T)
     D  rtnCode                      10I 0
      /free
           if (trigData.File.name <> 'GENLEDGER');
             return;
           endif;

           // Verify this is an Insert/Update + After Trigger
           if NOT (((trigData.Event = triggerEvent.Insert) or
                    (trigData.Event = triggerEvent.Update)) and
                    (trigData.EventTime = triggerTime.Before));
             return;
           endif;

           // Set the record format DS to the actual Before/After data.
           pOldData = %addr(trigData) + trigData.oldRecOffset;
           pNewData = %addr(trigData) + trigData.newRecOffset;

                ///////////////////////////////////////////////
                //  You must modify the fields used here to  //
                //  those in your own database file!         //
                ///////////////////////////////////////////////
           if (trigData.Event = triggerEvent.Update);
              afterImage.chgby = USRPRF;  // On Update set the Changed By user.
           else;
              afterImage.crtby = USRPRF; // On Insert set the
              afterImage.chgby = USRPRF; // Created and Changed By users.
           endif;
           return;
      /end-free