-- |>===========================================================================================================<|
-- | |
-- | ((>>--- SQL to prepare Terminal ID Manager for use. Tailor to your installation needs. ---<<)) |
-- | |
-- | 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;
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Create Pool definitions ---<<)) |
-- | Column values: |
-- | |
-- | Poolname -- Must be unique within the server. |
-- | pooltype -- Specify 1. |
-- | selectioncriteria -- Encode the attributes that must be matched to get an ID from this pool. |
-- | heartbeatinterval -- Number of seconds between the session sending an "I'm alive and using the ID" |
-- | message to the server. |
-- | commtimeoutinterval -- Number of seconds of non-communication before the server flags an ID with |
-- | Timed-Out status. |
-- | hold -- Set to 0. (Indicates 'in circulation'. Admin hold in Monitor ID dialog box |
-- | will set this value to 1.) |
-- | update_ts -- Initialize to today's date or a valid default, as indicated. Indicates the last |
-- | time this field was updated. |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into Pool
(Poolname, pooltype, selectioncriteria, heartbeatinterval, commtimeoutinterval, hold, update_ts )
VALUES
('Host3Sessions', 1, '_SessType_SessName_HostAddr_HostPort', 60, 3600, 0, '2010-07-06 10:00:00'),
('ClientIPAddrPool', 1, '_SessType_ClientIP', 70, 6000, 0, '2010-07-06 10:00:00'),
('UserNamePool', 1, '_SessType_UserName', 90, 1200, 0, '2009-12-10 10:00:00'),
('HostAddressPool', 1, '_SessType_HostAddr', 100, 360, 0, '2009-12-10 10:00:00'),
('HostPortPool', 1, '_SessType_HostPort', 120, 200, 0, '2009-12-10 10:00:00'),
('SessionPool', 1, '_SessType_PoolName', 180, 6000, 0, '2009-12-10 10:00:00'),
('ClientDNSPool', 1, '_SessType_ClientDNS', 70, 6000, 0, '2010-07-06 10:00:00'),
('4CriteriaPool', 1, '_SessType_AssocSet_UserName_SessName', 180, 6000, 0, '2009-12-10 10:00:00'),
('AllCriteriaPool', 1, '_SessType_AssocSet_PoolName_UserName_SessName_HostAddr_HostPort_ApplName_ClientIP_ClientDNS',
180, 6000, 0, '2009-12-10 10:00:00');
-- |>===========================================================================================================<|
-- | (Optional) Display the Pools just added: |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
select * from Pool;
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Create AssociationSet definitions ---<<)) |
-- | |
-- | This is an optional facility, and not useful to every installation of Terminal ID Manager. |
-- | |
-- | An AssociationSet provides a mechanism to group together ID definitions that represent |
-- | host-defined resources or addresses. |
-- | |
-- | The IDs in an AssociationSet typically: |
-- | --------------------------------------- |
-- | 1. Are used at the same time. |
-- | 2. Are used by the same end user. |
-- | 3. Correspond to identifiers that are defined on one or more related hosts. |
-- | 4. As a set, mirror some host-defined relationship between host resources; for instance, the host |
-- | relationship between a terminal and a set of printers. |
-- | |
-- | To use this facility, first create an AssociationSet for a given association scenario that is to |
-- | be used by a specific person. Give it a self-documenting name, such as "RJones-UTS-Gate27", or |
-- | "SF_Exec_Finance08". |
-- | |
-- | Ensure the target IDs are created, and make them members of the AssociationSet by setting the name of that |
-- | AssociationSet to the 'setname' field of each ID in the set. |
-- | |
-- | Configure the end user emulation session's Connection Setup to use Terminal ID Manager, and in the |
-- | ID Selection Setup dialog, select attributes as necessary to uniquely identify the target ID for each |
-- | session, matching the choice of ID selection attributes to the 'selectioncriteria' of the pool that |
-- | contains the target ID. |
-- | |
-- | Example: |
-- | |
-- | Create an AssociationSet called "Payroll_Admin_4West" in order to provide a certain userX |
-- | with a terminal ID associated with some printer IDs. |
-- | |
-- | You might create a pool called "Set_Terminals" that has a Selection Criterial of Username, Session-name, |
-- | Session-type, and "ID association". You'd also create IDs in that pool with the correct attribute values |
-- | for each of their username, sessionname, and sessiontype fields. At least one of those IDs would contain |
-- | userX in its username field, and have a setname field value of "Payroll_Admin_4West". |
-- | |
-- | Likewise, you could create a pool called "Set_Printers" with perhaps a Selection Criterial of Session-name, |
-- | Session-type, and "ID association". You'd also create IDs in that pool with the correct attribute values |
-- | for each of their sessionname, and sessiontype fields, with the intended IDs for the user above having a |
-- | setname field containing 'Payroll_Admin_4West'. |
-- | |
-- | Configure the Terminal ID Manager attributes for your terminal session to use Username, Session-name, |
-- | Session-type, and ID association to match the selection criteria of the pools containing the terminal IDs. |
-- | |
-- | Configure the Terminal ID Manager attributes for your printer sessions to use Session-name, Session-type, |
-- | and ID association to match the selection criteria of the pools containing the printer IDs. |
-- | |
-- | At runtime, userX would start a terminal session that would obtain the proper terminal ID, and it would |
-- | appropriately be the one that is a member of the "Payroll_Admin_4West" AssociationSet. |
-- | |
-- | When userX starts the printer session that has the _AssocSet attribute, Pool "Set_Printers" is |
-- | searched (along with perhaps other pools) since it has the proper selection criteria. Since it |
-- | contains IDs that match userX session attributes and belong to an AssoicationSet which are already |
-- | allocated to 'userX', they would be the IDs that are provided during the ID request to the server. |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into AssociationSet ( setname, hold, update_ts )
VALUES ( 'UTS_associate', 0, '2009-12-10 10:00:00' );
-- |>===========================================================================================================<|
-- | (Optional) display the Association-Sets just added: |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
select * from AssociationSet;
-- |>===========================================================================================================<|
-- | |
-- | ((>>---<> Creating IDs -- The Basics <>---<<)) |
-- | |
-- | (A) All IDs are created in a Pool. |
-- | |
-- | (B) The name of the ID must be unique within the Pool. |
-- | |
-- | (C) The combination of the idname and the poolname uniquely identifies an ID. |
-- | |
-- | (D) All IDs in a pool share these attribute values that are established on the pool definition: |
-- | |
-- | 1. selection criteria -- |
-- | the attributes on the IDs that must match the attributes provided in the users |
-- | request when the emulator session requests an ID from the Terminal ID Manager server. |
-- | 2. the heartbeat interval defined on the pool. |
-- | 3. the communication timeout interval defined on the pool. |
-- | 4. the value in their 'poolname' field. |
-- | |
-- | (E) IDs always require valid values for these fields, regardless of the selection criteria on the pool. |
-- | |
-- | 1. idname -- unique within the pool, as mentioned. |
-- | 2. poolname -- the same for all IDs in the pool. (Used by optional selection criteria 'PoolName') |
-- | 3. sessiontype -- the target host environment (in lowercase). |
-- | (For required selection criteria '_SessType') |
-- | 4. allocated -- set to 0 (zero) to indicate the ID is currently not in use. |
-- | 5. timedout -- set to 0 (zero) to indicate not in communication time-out with the server. |
-- | 6. hold -- set to 0 (zero) to indicate the ID is not out of operation due to |
-- | Administrative 'hold'. |
-- | 7. update_ts -- set to today's date, or some valid initialization value. (Field cannot be NULL). |
-- | |
-- | (F) Fields that must have valid values to match an optional 'selection criteria' defined on the pool |
-- | |
-- | 1. username -- name of emulation user (selection criteria includes '_UserName') |
-- | 2. sessionname -- name of the emulation session (selection criteria includes '_SessName') |
-- | 3. hostIP -- the IP Address of the target host (selection criteria includes '_HostAddr') |
-- | 4. hostport -- the port of the target host (selection criteria includes '_HostPort') |
-- | 5. application -- host application to start on connect (selection criteria includes '_ApplName') |
-- | 6. clientaddress -- the IP Address of the emulation client (selection criteria includes '_ClientIP') |
-- | 7. setname -- member of this AssociationSet (selection criteria includes '_AssocSet') |
-- | client request does not provide the name, just that it is to come from a set |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
-- |>===========================================================================================================<|
-- | |
-- | ((>>---<> Session Type Values (for sessiontype field) <>---<<)) |
-- | |
-- | Session Type in AWS Value of sessiontype |
-- | ------------------- ----------------- |
-- | Airlines Printer airlinesroute |
-- | ALC alc |
-- | IBM 3270 ibm3270 |
-- | IBM 3270 Printer ibm3287 |
-- | IBM 5250 ibm5250 |
-- | IBM 5250 Printer ibm3812 |
-- | T27 t27 |
-- | T27 Printer t27printer |
-- | UTS Terminal uts |
-- | UTS INT1 Environment uts |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
-- |>===========================================================================================================<|
-- | |
-- | ** NOTE ** There are limitations to the multi statement insert. |
-- | |
-- | The statements below insert multiple IDs, each bracketed by a pair of parentheses, separated by a comma. |
-- | A semi-colon terminates the statement and inserts multiple rows with a single commit operation to the DB.|
-- | However, because of a known bug in the Derby database engine (DERBY-1735), including too many inserts in |
-- | one statement creates a failure shown as "ERROR XJ001: Java exception: ': java.lang.StackOverflowError'."|
-- | |
-- | Avoid the StackOverflowError by limiting the number of bytes in combined statements to about 275K bytes. |
-- | As a rough guide for limiting how many rows can be inserted in a single statement, two examples are: |
-- | -- 4000 insert rows where the rows average 70 bytes in length. |
-- | -- 1900 insert rows where the rows average 150 bytes in length. |
-- | |
-- | The StackOverflowError will result in no rows being inserted. You can generally fix and rerun the |
-- | failed statement by dividing it in half, terminating the first half with a semi-colon instead of the |
-- | comma, and inserting another copy of the first three lines of that statement before the second half. |
-- | |
-- | For example: |
-- | ... |
-- | ( the first half rows... ), |
-- | ( 'value-1', 'value-2', 'value-3', etc.... ); <= note ending semi-colon instead of comma.|
-- | |
-- | insert into ID <= repeat these 3 lines |
-- | ( column-1, column-2, column-3, etc.... ) <= from the top |
-- | values <= of the first half |
-- | ( 'value-1', 'value-2', 'value-3', etc.... ), |
-- | ( ...remaining 2nd half rows); <= 2nd half statement ending semi-colon |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
-- |>===========================================================================================================<|
-- | Example ID definitions, showing various 'Selection Criteria' variations |
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Example: IDs for Selection Criteria '_SessType_ClientIP' ---<<)) |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into ID
( idname, poolname, sessiontype, allocated, timedout, hold, clientaddress, update_ts)
values
('idname771', 'ClientIPAddrPool', 'ibm3270', 0, 0, 0, '15.23.5.8', '2010-07-06 10:00:00'),
('idname772', 'ClientIPAddrPool', 'ibm3270', 0, 0, 0, '110.42.2.14', '2010-07-06 10:00:00'),
('idname773', 'ClientIPAddrPool', 'ibm3270', 0, 0, 0, '21.2.25.9', '2010-07-06 10:00:00');
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Example: IDs for Selection Criteria '_SessType_UserName' ---<<)) |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into ID
( idname, poolname, sessiontype, allocated, timedout, hold, username, update_ts)
values
('idname121', 'UserNamePool', 'alc', 0, 0, 0, 'PeggySue', '2010-07-06 10:00:00'),
('idname232', 'UserNamePool', 'alc', 0, 0, 0, 'BillyBob', '2010-07-06 10:00:00'),
('idname343', 'UserNamePool', 'alc', 0, 0, 0, 'MaryJane', '2010-07-06 10:00:00'),
('idname454', 'UserNamePool', 'alc', 0, 0, 0, 'SarahLee', '2010-07-06 10:00:00'),
('idname565', 'UserNamePool', 'alc', 0, 0, 0, 'MollyAnn', '2010-07-06 10:00:00'),
('idname676', 'UserNamePool', 'alc', 0, 0, 0, 'AnnaMarie', '2010-07-06 10:00:00'),
('idname787', 'UserNamePool', 'alc', 0, 0, 0, 'JohnDoe', '2010-07-06 10:00:00'),
('idname898', 'UserNamePool', 'alc', 0, 0, 0, 'BobbyJack', '2010-07-06 10:00:00');
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Example: IDs for Selection Criteria '_SessType_HostAddr' ---<<)) |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into ID
( idname, poolname, sessiontype, allocated, timedout, hold, hostIP, update_ts)
values
('idname987', 'HostAddressPool', 't27', 0, 0, 0, 'seamatt01.attm.com', '2010-07-06 10:00:00'),
('idname654', 'HostAddressPool', 't27', 0, 0, 0, '10.4.65.6', '2010-07-06 10:00:00'),
('idname321', 'HostAddressPool', 't27', 0, 0, 0, 'hostfin1.jett.net', '2010-07-06 10:00:00');
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Example: IDs for Selection Criteria '_SessType_HostPort' ---<<)) |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into ID
( idname, poolname, sessiontype, allocated, timedout, hold, hostport, update_ts)
values
('idname987', 'HostPortPool', 'ibm5250', 0, 0, 0, 24, '2010-07-06 10:00:00'),
('idname654', 'HostPortPool', 'ibm5250', 0, 0, 0, 2424, '2010-07-06 10:00:00'),
('idname321', 'HostPortPool', 'ibm5250', 0, 0, 0, 3800, '2010-07-06 10:00:00');
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Example: IDs for Selection Criteria '_SessType_SessName_HostAddr_HostPort' ---<<)) |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into ID
( idname, poolname, sessiontype, allocated, timedout, hold, sessionname, hostIP, hostport, update_ts)
values
('idname101', 'Host3Sessions', 'ibm3270', 0, 0, 0, 'HostXYZ01', '10.2.25.4', 23, '2010-07-06 10:00:00'),
('idname202', 'Host3Sessions', 'ibm3270', 0, 0, 0, 'HostABC01', '10.2.17.1', 2323, '2010-07-06 10:00:00'),
('idname303', 'Host3Sessions', 'ibm3270', 0, 0, 0, 'HostXYZ01', '10.2.22.9', 29, '2010-07-06 10:00:00');
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Example: IDs for Selection Criteria '_SessType_PoolName' ---<<)) |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into ID ( idname, poolname, sessiontype, allocated, timedout, hold, update_ts )
values
('idname001' , 'SessionPool', 'ibm3270', 0, 0, 0, '2010-07-06 10:00:00' ),
('idname002' , 'SessionPool', 'ibm3270', 0, 0, 0, '2010-07-06 10:00:00' ),
('idname003' , 'SessionPool', 'ibm3270', 0, 0, 0, '2010-07-06 10:00:00' ),
('idname004' , 'SessionPool', 'airlinesroute', 0, 0, 0, '2010-07-06 10:00:00' ),
('idname005' , 'SessionPool', 'alc', 0, 0, 0, '2010-07-06 10:00:00' ),
('idname006' , 'SessionPool', 't27' , 0, 0, 0, '2010-07-06 10:00:00' ),
('idname007' , 'SessionPool', 'uts', 0, 0, 0, '2010-07-06 10:00:00' ),
('idname008' , 'SessionPool', 'ibm3812', 0, 0, 0, '2010-07-06 10:00:00' ),
('idname009' , 'SessionPool', 'ibm3270', 0, 0, 0, '2010-07-06 10:00:00' );
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Example: IDs for Selection Criteria '_SessType_ClientDNS' ---<<)) |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into ID ( idname, poolname, sessiontype, allocated, timedout, hold, clientdns, update_ts)
values
('idname774', 'ClientDNSPool', 'ibm3270', 0, 0, 0, 'computerA.mydomain.com', '2010-07-06 10:00:00'),
('idname775', 'ClientDNSPool', 'ibm3270', 0, 0, 0, 'computerB.mydomain.com', '2010-07-06 10:00:00'),
('idname776', 'ClientDNSPool', 'ibm3270', 0, 0, 0, 'computerC.mydomain.com', '2010-07-06 10:00:00');
-- |>===========================================================================================================<|
-- | |
-- | ((>>--- Example: IDs for Selection Criteria '_SessType_AssocSet_UserName_SessName' ---<<)) |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into ID
( idname, poolname, sessiontype, allocated, timedout, hold, sessionname, username, update_ts, setname)
values
('idname101', '4CriteriaPool', 'uts', 0, 0, 0, 'MySession1', 'PeggySue', '2010-07-06 10:00:00', 'UTS_associate' ),
('idname202', '4CriteriaPool', 'uts', 0, 0, 0, 'MySession1', 'BillyBob', '2010-07-06 10:00:00', 'UTS_associate' ),
('idname303', '4CriteriaPool', 'uts', 0, 0, 0, 'MySession2', 'MaryJane', '2010-07-06 10:00:00', 'UTS_associate' ),
('idname405', '4CriteriaPool', 'uts', 0, 0, 0, 'MySession2', 'BobbyJoe', '2010-07-06 10:00:00', 'UTS_associate' ),
('idname505', '4CriteriaPool', 'uts', 0, 0, 0, 'MySession3', 'SarahLee', '2010-07-06 10:00:00', 'UTS_associate' ),
('idname606', '4CriteriaPool', 'uts', 0, 0, 0, 'MySession3', 'MollyAnn', '2010-07-06 10:00:00', 'UTS_associate' );
-- |>=================================================================================================================================================<|
-- | |
-- | ((>>--- Example: IDs for Selection Criteria '_SessType_AssocSet_PoolName_UserName_SessName_HostAddr_HostPort_ApplName_ClientIP_ClientDNS' ---<<)) |
-- | |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
insert into ID
( idname, poolname, sessiontype, allocated, timedout, hold, sessionname, username, clientaddress, clientdns, hostIP, hostport, application, update_ts, setname)
values
('idname101', 'AllCriteriaPool', 'uts', 0, 0, 0, 'MySession1', 'PeggySue', '10.2.2.1', 'computerA.mydomain.com', '10.2.25.4', 12, 'Mapper', '2010-07-06 10:00:00', 'UTS_associate' ),
('idname202', 'AllCriteriaPool', 'uts', 0, 0, 0, 'MySession1', 'BillyBob', '10.2.2.2', 'computerB.mydomain.com', '10.3.55.2', 12, 'Mapper', '2010-07-06 10:00:00', 'UTS_associate' ),
('idname303', 'AllCriteriaPool', 'uts', 0, 0, 0, 'MySession2', 'MaryJane', '10.2.2.3', 'computerC.mydomain.com', '10.4.65.6', 12, 'Mapper', '2010-07-06 10:00:00', 'UTS_associate' ),
('idname404', 'AllCriteriaPool', 'uts', 0, 0, 0, 'MySession2', 'BobbyJoe', '10.2.2.4', 'computerD.mydomain.com', '10.5.85.9', 12, 'Mapper', '2010-07-06 10:00:00', 'UTS_associate' ),
('idname505', 'AllCriteriaPool', 'uts', 0, 0, 0, 'MySession3', 'SarahLee', '10.2.2.5', 'computerE.mydomain.com', '10.6.99.7', 12, 'Mapper', '2010-07-06 10:00:00', 'UTS_associate' ),
('idname606', 'AllCriteriaPool', 'uts', 0, 0, 0, 'MySession3', 'MollyAnn', '10.2.2.6', 'computerF.mydomain.com', '10.7.11.1', 12, 'Mapper', '2010-07-06 10:00:00', 'UTS_associate' );
-- |>===========================================================================================================<|
-- | (Optional) display the IDs, or the IDs in a particular Pool, or the IDs in a particular Association-Set |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
select * from ID;
select idname, poolname, sessiontype, sessionname, hostIP, hostport from ID where POOLNAME = 'Host3Sessions';
select idname, poolname, sessiontype, username from ID where SETNAME = 'UTS_associate';
select idname, poolname, sessiontype, setname, username, sessionname, hostIP, hostport, application, clientaddress, clientdns from ID where POOLNAME = 'AllCriteriaPool';
-- |>===========================================================================================================<|
-- | Always the final statement - do not Modify or comment. This terminates the 'ij' command processor |
-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|
EXIT;