-- |>===========================================================================================================<|-- | |-- | ((>>--- 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. |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|SETSCHEMAIDM;-- |>===========================================================================================================<|-- | |-- | ((>>--- 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. |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoPool(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*fromPool;-- |>===========================================================================================================<|-- | |-- | ((>>--- 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. |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoAssociationSet(setname,hold,update_ts)VALUES('UTS_associate',0,'2009-12-10 10:00:00');-- |>===========================================================================================================<|-- | (Optional) display the Association-Sets just added: |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|select*fromAssociationSet;-- |>===========================================================================================================<|-- | |-- | ((>>---<> 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' ---<<)) |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoID(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' ---<<)) |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoID(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' ---<<)) |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoID(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' ---<<)) |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoID(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' ---<<)) |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoID(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' ---<<)) |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoID(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' ---<<)) |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoID(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' ---<<)) |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoID(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' ---<<)) |-- | |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|insertintoID(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*fromID;selectidname,poolname,sessiontype,sessionname,hostIP,hostportfromIDwherePOOLNAME='Host3Sessions';selectidname,poolname,sessiontype,usernamefromIDwhereSETNAME='UTS_associate';selectidname,poolname,sessiontype,setname,username,sessionname,hostIP,hostport,application,clientaddress,clientdnsfromIDwherePOOLNAME='AllCriteriaPool';-- |>===========================================================================================================<|-- | Always the final statement - do not Modify or comment. This terminates the 'ij' command processor |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|EXIT;