Update Tables

-- |>===========================================================================================================<|
-- |                                                                                                             |
-- |              ((>>--- Examples of various Terminal ID Manager database functions ---<<))                     |
-- |                                                                                                             |
-- |        Lines beginning with "--" are comments; all others must be valid SQL for Terminal ID Manager.        |
-- |                                                                                                             |
-- |>===========================================================================================================<|

-- |>===========================================================================================================<|
-- |                                                                                                             |
-- |  Do not modify or comment these statements - they obtain a connection to                                    |
-- |  the Terminal ID Manager Derby database, called DerbyIDM.                                                   |
-- |                                                                                                             |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
SET SCHEMA IDM;

-- |>===========================================================================================================<|
-- |                                                                                                             |
-- |                   ((>>--- Example: Update one or more values of a pool definition  ---<<))                  |
-- |                                                                                                             |
-- |    Note:                                                                                                    |
-- |                                                                                                             |
-- |     * 'heartbeatinterval' and 'commtimeoutinterval' represent a number of seconds.  The Monitor IDs tab     |
-- |       shows those values in minutes (seconds divided by 60).  Since conversion of seconds to minutes        |
-- |       rounds down during division, 119 seconds will show as 1 minute, while 120 will show as 2 minutes.     |
-- |                                                                                                             |
-- |       If you set 'heartbeatinterval' to less than 60 seconds, a default minimum of                          |
-- |       60 seconds will be established for that value.                                                        |
-- |                                                                                                             |
-- |       If you set 'commtimeoutinterval' to less than 180 seconds, a default minimum of                       |
-- |       180 seconds will be established for that value.                                                       |
-- |                                                                                                             |
-- |       To allow 'heartbeatinterval' and 'commtimeoutinterval' for a pool to be controlled by the minutes     |
-- |       values specified on the Monitor IDs tab fields of "Heartbeat interval" or                             |
-- |       'Communication timeout interval', set the value on that pool field to -1.                             |
-- |                                                                                                             |
-- |     * When updating selectioncriteria on a pool definition, you must be certain that all IDs defined in     |
-- |       that pool have valid values specified for the attributes that are listed in the 'selectioncriteria'.  |
-- |                                                                                                             |
-- |       Example: if you change a selectioncriteria from '_SessType_SessName' to '_SessType_SessName_UserName',|
-- |       on the "PoolXYZ' pool, you must add valid values to the 'username' field of each ID in PoolXYZ.       |
-- |                                                                                                             |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|

--  display all the columns of the Pool table
SELECT poolname, pooltype, selectioncriteria, heartbeatinterval, commtimeoutinterval, hold, update_ts FROM Pool;


-- update all pools with the same values for heartbeatinterval and commtimeoutinterval

UPDATE Pool SET heartbeatinterval = 65, commtimeoutinterval = 960;



-- update pools 'HostPortPool' and 'ClientIPAddrPool' with the same heartbeat and comm. timeout values

UPDATE Pool SET heartbeatinterval = 91, commtimeoutinterval = 239 WHERE poolname = 'HostPortPool' or poolname = 'ClientIPAddrPool';



-- update pools 'UserNamePool' and '3270PoolTiny' to take the server default values for heartbeat and comm. timeout intervals

UPDATE Pool SET heartbeatinterval = -1, commtimeoutinterval = -1 WHERE poolname = 'UserNamePool' or poolname = '3270PoolTiny';




-- update pool 'UsersPool' to remove the _UserName attribute from its '_SessType_SessName_UserName' selection criteria
-- then remove the 'username' values from the IDs that are in pool 'UsersPool'

UPDATE Pool SET selectioncriteria = '_SessType_SessName' where selectioncriteria = '_SessType_SessName_UserName' and poolname = 'UsersPool';
UPDATE ID   SET username = null where poolname = 'UsersPool';



-- display the heartbeat and comm-timeout values for all pools

SELECT POOLNAME, HEARTBEATINTERVAL , COMMTIMEOUTINTERVAL  FROM POOL; 




-- |>===========================================================================================================<|
-- |                                                                                                             |
-- |                   ((>>--- Example: Update one or more values of an ID definition  ---<<))                   |
-- |                                                                                                             |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|


--  change the sessionname value on IDs for a particular user
update ID set SESSIONNAME = 'mySession' where USERNAME = 'userxyz' and POOLNAME = 'somepool';


-- add some IDs to an AssociationSet

update ID set SETNAME = 'myUTS' where IDNAME = 'myUTSID1';
update ID set SETNAME = 'myUTS' where IDNAME = 'myUTSID2';
update ID set SETNAME = 'myUTS' where IDNAME = 'myUTSID3';



-- |>===========================================================================================================<|
-- |                                                                                                             |
-- |               ((>>--- Example: Update one or more values of an AssociationSet definition  ---<<))           |
-- |                                                                                                             |
-- |      Rename the name of an association set, and also rename the set reference to all the IDs in that set.   |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|


UPDATE ASSOCIATIONSET set SETNAME = 'UtsSet' where SETNAME = 'myUTS';
UPDATE ID set SETNAME = 'UtsSet' where SETNAME = 'myUTS';


-- |>===========================================================================================================<|
-- |  Always the final statement - do not Modify or comment.  This terminates the 'ij' command processor         |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
EXIT;