-- |>===========================================================================================================<|-- | |-- | ((>>--- SQL to delete Terminal ID Manager operational data ---<<)) |-- | |-- |>===========================================================================================================<|-- | |-- | This script contains sample statements to delete all or parts of the operational Pool, |-- | AssociationSet, and ID data of the Terminal ID Manager's Derby database. |-- | |-- | |-- | This script assist users with deleting data, but does not remove the Pool, AssociationSet, |-- | or ID tables from the database. |-- | |-- | |-- | Note: |-- | |-- | * Names of IDs, Pools, and AssociationSets are case-sensitive. |-- | |-- | * Lines beginning with "--" are comments; all others must be valid SQL for Terminal ID Manager. |-- | |-- | * Uncomment only the SQL statements to be executed. | -- | |-- | * A Pool can be deleted only if it does not contain any IDs. |-- | |-- | * To delete an AssociationSet, you must first change the setname of each ID that is a |-- | member of that AssociationSet to NULL. |-- | |-- |>===========================================================================================================<|-- |>===========================================================================================================<|-- | | -- | Do not modify or comment these statements - they obtain a connection to |-- | the Terminal ID Manager Derby database, called DerbyIDM. |-- | | -- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|SETSCHEMAIDM;-- |>===========================================================================================================<|-- | | -- | ((>>--- Statements to delete all ID, Pool, and AssociationSet data ---<<)) |-- | | -- | ==> Execute these statements to empty the Terminal ID Manager database tables. |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|-- removes all the ID and Pool and AssociationSet definitions-- delete from ID;-- delete from AssociationSet;-- delete from Pool;-- |>===========================================================================================================<|-- | | -- | (>>--- Statements to remove or delete one or more IDs from Pools and AssociationSets ---<<)) |-- | | -- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|-- delete all the IDs contained in all of the Pools and AssociationSets-- delete from ID;-- delete all the IDs from one Pool. -- delete from ID where poolname = 'Name_Of_Pool_to_Empty';-- removes all the IDs currently members of an AssociationSet. Note that the IDs are not deleted. -- update ID set setname = NULL where setname = 'Name_of_AssociationSet_to_empty';-- moves all the IDs from one AssociationSet that are from a given Pool to a different AssociationSet. -- update ID set setname = 'To_AssocSet' where setname = 'From_AssociationSet' and poolname = 'Name_Of_Pool';-- delete all the IDs with a name matching a particular pattern where those IDs are from an AssociationSet -- that has a name matching a particular pattern and are defined in a Pool whose name has a particular pattern. -- For example: this statement might delete IDs 'myTest1234' and 'newTest5678' if they are defined in Pool 'FinalTest'-- and are members of the AssociationSet 'TestPayroll'. -- delete from ID where idname like '%Test%' and setname like 'TEST%' and poolname like '%Test';-- |>===========================================================================================================<|-- | | -- | ((>>--- Delete one or more Pool definitions ---<<)) |-- | | -- | ==> Pool must not contain any IDs for delete to succeed | -- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|-- removes all the Pool definitions from Terminal ID Manager. Deleting all IDs empties all Pools so they can be deleted.-- delete from ID;-- delete from Pool;-- remove one Pool definition from Terminal ID Manager. Name of Pool is case-sensitive. Pool must be empty before deleting.-- delete from ID where poolname = 'Name_Of_Pool_To_Delete';-- delete from Pool where poolname = 'Name_Of_Pool_To_Delete';-- |>===========================================================================================================<|-- | | -- | ((>>--- Delete one or more Association Set definitions ---<<)) |-- | | -- | ==> Remove all IDs from an AssociationSet before the AssociationSet is deleted. An ID is | -- | removed from an AssociationSet by setting the 'setname' field in the ID to NULL. | -- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|-- removes all the AssociationSet definitions from Terminal ID Manager-- update ID set setname = NULL where setname IS NOT NULL;-- delete from AssociationSet;-- removes one AssociationSet definition from Terminal ID Manager. Remove the IDs that are members of the AssociationSet first.-- update ID set setname = NULL where setname = 'Name_Of_AssociationSet_To_Delete';-- delete from AssociationSet where setname = 'Name_Of_AssociationSet_To_Delete';-- remove all AssociationSets with names matching a pattern. Removes IDs from those AssociationSets first but-- does not delete them. -- example: will remove IDs from set membership, and then delete AssociationSets 'ABCzzzz123', 'zzzzXYZ', and '123zzzz'.-- update ID set setname = NULL where setname like '%zzzz%';-- delete from AssociationSet where setname like '%zzzz%';-- |>===========================================================================================================<|-- | Always the final statement - do not Modify or comment. This terminates the 'ij' command processor |-- |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -|EXIT;