Contents: SQL Tools, Administration
SQL Tools
A number of SQL scripts are provided for Aida directory service management. These are in the CD_SOFT SQl script release directory "/afs/slac/g/cd/soft/dev/ora/". That directory is in ORACLE_PATH, which is set by sourcing /afs/slac/g/cd/soft/dev/script/oracle_env.csh.
The Aida Directory Service database schema is given here.
Setup
To set yourself up to execute these tools, log into a SLAC Public AFS Solaris machine (Tersk, Flora etc) and then, first, set your environment:
[tersk02]:u/cd/greg> source /afs/slac/g/cd/soft/dev/script/ENVS.csh [tersk02]:u/cd/greg> source !:1:h/oracle_env.csh
Then enter a sql interpreter like sqlplus. You give the Aida database on which you will be performing operations. On tersk this is:
sqlplus AIDADEV/<password>
You'll need to ask an Oracle administrator for the password.
Management SQL scripts
This subsection describes the use of some of the many SQL scripts that you can find in /afs/slac/g/cd/soft/package/aida/common/script/.
Show Aida Services
To show names and ID numbers of all Aida services in the Aida directory service:
@show_services
To Add a Name to Aida.
To add a name to Aida, run add_IA.sql, giving the number of the Aida service to which you want to add the name, plus the instance and attribute of the name:
@add_IA 63 'BPMS:PR02:8032' 'TWISS'
You can find all the existing Aida service names with show_services.sql. Add the name first to AIDADEV, then test; if successful, add the name to AIDAPROD.
To Add a Name with a Transform to Aida
A "transform" is a pattern that is sent to the data provider in place of the given name. It's used for instance to provide the SQL query that the RDB data provider should run to get the data for a name. To add a name and transform, use add_IAT.sql. The following example tells Aida to pass a sql select SELECT statement to the Database data provide (number 5 - see show_services), when asked for BPMS:IN20//BSA.X1H.NAMES.
@add_IAT 5 "BPMS:IN20" "BSA.X1H.NAMES" "select unique instance from aida_names where instance like ''BPMS:IN20:%:X1H''"
Note the use of quotes - you must quote the transform so it's a single argument to add_IAT.sql, and if teh transform itself includes quotes as this one does, then they must be double-quoted in sqlplus.
Add the name first to AIDADEV, then test; if successful, add the name to AIDAPROD.
Verify presence of a name
To verify existence of an aida name, and to get its AIDA_NAMES table ID:
@show_IA 'BPMS:IA20:235' 'twiss'
To use wildcards, or get the transform etc, use the APEX interface.
To Remove a Name from Aida.
To remove an name from Aida, run remove_IA.sql, giving the name's instance and attribute:
SQL> @remove_IA 'LCLS' 'BSA.rootnames.byZ'
Remove the name first from AIDADEV, then test, then it from AIDAPROD too.
To Change the Transform of a Name
To change the transform Aida sends a data provider for a given instance-attribute name, use update_IAT.sql, giving the instance, attribute, and new transform. The following example changed the transform associated with the Aida name LCLS//BSA.elements.byZ:
@update_IAT 'LCLS' 'BSA.elements.byZ' 'SELECT ROOT_NAME, SOURCE, ELEMENT, BEAMLINE, AREA, LINACZ_M FROM LCLS_INFRASTRUCTURE.V_LCLS_BSA ORDER BY LINACZ_M'
Change the transform first in AIDADEV, then test; if successful, change the transform in AIDAPROD.
To Add a Transform to an Existing Name
To make Aida send a transform to the data provider for a given instance-attribute name, use update_IAT.sql, just as you would have had the name had a transform associated with it before.
Add the transform first in AIDADEV, then test; if successful, add the transform in AIDAPROD.
Administration
This section presents documentation useful to the Database administrators and developers responsible for the maintenance of the Aida directory service. This information is at a lower level than the SQL Tools presented above.
The Aida Directory Service database schema AIDA_Dir_Service_Db_Schema.pdf.
The Schema DDL of AIDADEV (identical to AIDAPROD) contains a listing of all table definitions, triggers and Stored Procedure listsings used by AIDA.