1.4. AdminShell 1.4.1. AdminShell Features AdminShell provides the following features: Administration AdminShell can be used to connect to a JBoss Data Virtualization instance in order to perform various administrative tasks. Data Access AdminShell can be used to connect to a virtual database (VDB) and run SQL commands to query VDB data and view results. Migration AdminShell can be used to develop scripts that will move VDBs and associated components from one development environment to another. (Users can test and automate migration scripts before executing them in production deployments.) Testing The built-in JUnit Test Framework allows users to write regression tests to check system health and data integrity. The written tests validate system functionality automatically, removing the need for manual verification by QA Personnel. 1.4.2. AdminShell Scripting Use the Groovy language to write AdminShell scripts to automate tasks. AdminShell provides a custom library of Groovy functions to help administer JBoss Data Virtualization. These custom functions can be used in isolation or alongside other Groovy code. Here are some basic rules. Memorise these to become familiar with the syntax: All commands and functions are case sensitive. Groovy commands are not required to end with a semicolon; this is optional. A function will take zero or more parameters as input. Function parameters are provided within parentheses. String parameters must be provided within single or double quotes. Other Java classes and methods can be accessed and invoked from within scripts as long as required libraries are already in the class path. (JAR files added to the lib directory will be included in the class path automatically.) Note You should disconnect from JBoss Data Virtualization before exiting AdminShell by entering the disconnect() command. References For more information about Groovy scripting, refer to http://groovy.codehaus.org/ . For more information about Groovy scripting and SQL, refer to http://groovy.codehaus.org/Database+features . For more information about testing using Groovy scripting, refer to http://groovy.codehaus.org/Unit+Testing and http://junit.org/ . 1.4.3. AdminShell Help All of the custom administrative methods available in AdminShell can be listed by using the adminHelp() method (note that only the custom AdminShell methods will be shown): If you require a specific method's definition and input parameters, use adminHelp(" METHOD ") : Use the sqlHelp() method to list all SQL extension methods: If you want to obtain a specific method's definition and input parameters, use sqlHelp(" METHOD ") . 1.4.4. Install the Interactive AdminShell The AdminShell can be installed and run from any machine that has access to the running Red Hat JDV server. Procedure 1.3. Install the AdminShell Anywhere Open a command line terminal. Go to the AdminShell directory: cd / EAP_HOME /dataVirtualization/teiid-adminshell/ . Copy the zipped teiid-VERSION-adminshell-dist.zip file to your desired location. Unzip the AdminShell: unzip teiid-VERSION-adminshell-dist.zip 1.4.5. Launch the Interactive AdminShell Procedure 1.4. Launch the Interactive AdminShell Run the ./adminshell.sh command. The log output is as follows: Result The interactive AdminShell is running. At this point you can execute individual commands line by line. Note You can exit the interactive AdminShell at any time by entering the exit command. 1.4.6. AdminShell Basic Commands Table 1.1. AdminShell Basic Commands Command Description adminHelp(); Displays all of the custom AdminShell methods available. adminHelp(" METHOD "); Displays the definition and input parameters for the AdminShell method supplied. sqlHelp(); Displays all of the custom SQL AdminShell methods available. sqlHelp(" METHOD "); Displays the definition and input parameters for the SQL AdminShell method supplied. println " STRING "; Print something to console. sql = connect(); Get an extended Groovy SQL connection using the default connection specified in the connection.properties file. sql.execute(" SQL "); Run any SQL command. connectAsAdmin(); Connects as an admin user using the default connection specified in the connection.properties file. This command does not require a VDB name. Note that this is an admin connection, not a JDBC connection, so you cannot issue SQL commands using this connection. Note that if SSL is being used, you would need to adjust the connection URL and the client SSL settings as necessary (typically only for 2-way SSL). println getConnectionName(); Returns the active connection name. useConnection(" cNAME "); Switches to using the given connection. disconnect(); Disconnects the active connection. disconnectAll(); Disconnects all connections (both active and suspended). 1.4.7. Execute a Script in Non-interactive AdminShell Procedure 1.5. Execute a Script in Non-interactive AdminShell Open a Command Line Terminal Run the Script Run the /adminshell.sh load [-D param=value ] PATH/FILENAME command. Note Optional properties passed in using -D can be accessed from within the script file by using System.getProperty() . Important Any connections established within the script should be disconnected before the script finishes. 1.4.8. Helpful Tips for the Interactive AdminShell Table 1.2. Interactive AdminShell Commands Command Description ^ ('up arrow') Displays commands executed, starting with the most recent. help Displays additional commands provided specifically for use within the interactive shell. Shell Behavior The interactive shell uses a special interpreter and displays behavior different from what would be expected from running a Groovy script: def statements do not define a variable in the context of the shell; For example, do not use def x = 1 , use x = 1 . The shell cannot parse Groovy classes that use annotations. 1.4.9. Save a Script in Interactive AdminShell Procedure 1.6. Save a Script in Interactive AdminShell Open the Interactive AdminShell Open a command line terminal. Navigate to / EAP_HOME /dataVirtualization/teiid-adminshell- VERSION / . Run the ./adminshell.sh command. Start Recording to a File Enter the record start PATH/FILENAME command. Enter Desired Commands to Record Enter a series of commands for which you want to record the input/output. Stop Recording to the File Enter the record stop command. Result All input and output between the moments you issue the record start and record stop commands are captured in PATH/FILENAME . Note Since both input and output are recorded, the file will need some editing before it can be executed as a script file. 1.4.10. Execute a Script in Interactive AdminShell Procedure 1.7. Execute a Script in Interactive AdminShell Open the Interactive AdminShell Open a command line terminal. Navigate to / EAP_HOME /dataVirtualization/teiid-adminshell- VERSION / . Run the ./adminshell.sh command. Run the Script Run the Script using the Interactive Shell load Command Run the load PATH/FILENAME command. Run the Script using the Groovy evaluate Command Run the evaluate(" PATH/FILENAME " as File) command. Note You can also execute a script by entering it line by line after opening the interactive shell. 1.4.11. Launch the AdminShell GUI Procedure 1.8. Launch the AdminShell GUI Navigate to the adminshell Directory Open a command line terminal. Navigate to / EAP_HOME /dataVirtualization/teiid-adminshell- VERSION / . Launch the adminshell-console Script Run the ./adminshell-console.sh command. Result The AdminShell GUI is displayed. Note You can exit the AdminShell GUI at any time by clicking on File Exit . 1.4.12. Save a Script in AdminShell GUI Procedure 1.9. Save a Script in AdminShell GUI Navigate to the adminshell Directory Open a command line terminal. Navigate to / EAP_HOME /dataVirtualization/teiid-adminshell- VERSION / . Type a Script Type your script in the upper script window of the AdminShell GUI. You may find it useful to test the script as you are preparing it by executing it via Script Run . Save the Script Select File Save As . Choose a location and filename for the script and click on Save . 1.4.13. Execute a Script in AdminShell GUI Procedure 1.10. Execute a Script in AdminShell GUI Open the AdminShell GUI Open a command line terminal. Navigate to / EAP_HOME /dataVirtualization/teiid-adminshell- VERSION / . Run the ./adminshell-console.sh command. Prepare the Script Type a New Script Type your script in the upper script window of the AdminShell GUI. Load a Saved Script Select File Open . Locate the desired script file and click on Open . The script will appear in the upper script window of the AdminShell GUI. Run the Script Select Script Run . 1.4.14. AdminShell Connection Properties The EAP_HOME /dataVirtualization/teiid-adminshell- VERSION /connection.properties file provides the default connection properties used by AdminShell to connect to an JBoss Data Virtualization instance: A call to connect() or connectionAsAdmin() without any input parameters will connect to JBoss Data Virtualization using the settings defined in this properties file. connect() uses those properties prefixed with jdbc and connectAsAdmin() uses those properties prefixed with admin . Alternatively, you can include parameters in the connect() or connectAsAdmin() methods to connect using properties of your choice: Warning Do not leave passwords stored in clear text. The example above is for demonstration purposes only. If you want to store your password in the file, take the necessary measures to secure it. Otherwise, do not use this feature at all and, instead, input your password interactively (or some other secure way.) 1.4.15. Multiple Connections in AdminShell Using AdminShell, users can manage more than one connection to one or more JBoss Data Virtualization instances. For example, a user might want to have one connection to a development server and another to an integration server, simultaneously. Every time a new connection is made, it is assigned a unique name and it becomes the active connection. If there is already a connection, it will be suspended (it will not be closed). The getConnectionName() method returns the name of the active connection. The connection name can be assigned to a variable cName by the following command: The name of the current connection is required in order to switch from one to another. To change the active connection, use the useConnection() command, supplying the name (or a variable with the name assigned) of the connection you wish to use: Example The following example demonstrates how to use two connections and switch between them: 1.4.16. Example Scripts Example 1.1. Deploying a VDB Example 1.2. Create a Datasource (Oracle) Example 1.3. Execute SQL Query against Teiid 1.4.17. AdminShell FAQ Q: Why won't the adminhelp command work in the GUI tool? Q: Are there any pre-built scripts available? Q: What is the difference between connectAsAdmin() and connect()? Q: What does getAdmin() do? Why do I need it? Q: Is IDE support available for writing the scripts? Q: Can I use AdminShell methods in other environments? Q: Is debugging support available? Q: Why won't the adminhelp command work in the GUI tool? A: The AdminShell GUI environment does not understand shell commands such as load , help , and adminhelp , since they are not directly supported by Groovy. In the GUI you should use the equivalent functional form; for example, instead of using adminhelp use adminHelp() . Q: Are there any pre-built scripts available? A: Not currently. Q: What is the difference between connectAsAdmin() and connect() ? A: The connectAsAdmin() method creates a contextual connection to the AdminAPI of JBoss Data Virtualization. The connect() method returns an extension of the Groovy SQL object to be used for SQL calls to JBoss Data Virtualization. Q: What does getAdmin() do? Why do I need it? A: The getAdmin() method returns the current contextual connection object that was created when you connected with connectAsAdmin() . This object implements the interface org.teiid.adminapi.Admin . The AdminShell commands provided are wrappers around this API. Advanced users can use this API directly if the provided wrapper commands do not meet their needs. Q: Is IDE support available for writing the scripts? A: The AdminShell GUI tool is a light-weight IDE. Full IDE support is available for Groovy, but requires manual manipulation of the class path and script imports. Q: Can I use AdminShell methods in other environments? A: The AdminShell methods (including the named contextual connections, AdminAPI wrapper, and help system) have no direct dependencies on Groovy and can be used in other scripting languages. To use the AdminShell methods in another language, simply import the static methods and Admin classes into your script. You will also need to ensure that the EAP_HOME /dataVirtualization/teiid-adminshell- VERSION /lib/teiid- VERSION .jar and EAP_HOME /dataVirtualization/teiid-adminshell- VERSION /lib/teiid-adminshell- VERSION .jar are in your class path. The following snippet shows import statements that would work in Java, BeanShell, Groovy and others: import static org.teiid.adminshell.AdminShell.*; import org.teiid.adminapi.*; Q: Is debugging support available? A: The Interactive AdminShell and GUI have built-in support for inspection of the current state; however, line based debugging is beyond the scope of this document.

Commands:
connectAsAdmin("localhost", "9999", "user", "password", "conn1")
import my.package.*; myObject = new MyClass(); myObject.doSomething();
adminHelp()
adminHelp("deploy") /* *Deploy a VDB from file */ void deploy( String /* file name */) throws AdminException throws FileNotFoundException
sqlHelp()
====================================================================== Teiid AdminShell Bootstrap Environment TEIID_HOME = / EAP_HOME /dataVirtualization/teiid-adminshell- VERSION CLASSPATH = / EAP_HOME /dataVirtualization/teiid-adminshell- VERSION /lib/patches/*:/ EAP_HOME /dataVirtualization/teiid-adminshell- VERSION /lib/teiid-adminshell- VERSION .jar:/ EAP_HOME /dataVirtualization/teiid-adminshell- VERSION /lib/* JAVA = /usr/lib/jvm/java-1.7.0-openjdk.x86_64/bin/java ====================================================================== ===> [import static org.teiid.adminshell.AdminShell.*; import static org.teiid.adminshell.GroovySqlExtensions.*; import org.teiid.adminapi.*;] Groovy Shell (1.7.2, JVM: 1.7.0_25) Type 'help' or '\h' for help. ------------------------------------------------------------------------------- groovy:000>
value = System.getProperty(" param ")
jdbc.user=user jdbc.password=user jdbc.url=jdbc:teiid:admin@mm://localhost:31000; admin.host=localhost admin.port=9999 admin.user=admin admin.password=admin
connect(" URL ", " USER ", " PASSWORD ")
cName = getConnectionName();
useConnection(cName);
// Create a connection connectAsAdmin(); //Assign the connection name to conn1 conn1 = getConnectionName(); deploy("file.vdb") // Create a second connection (which is now the active connection) connectAsAdmin(); //Assign the new connection name to conn2 conn2 = getConnectionName(); deploy("file.vdb") // Switch the active connection back to conn1 useConnection(conn1); // Close the active connection (conn1) disconnect();
connectAsAdmin(); deploy("/path/to/<name>.vdb"); // check to validate the deployment VDB vdb = getVDB("<name>", 1); if (vdb != null){ print (vdb.getName()+"."+vdb.getVersion()+" is deployed"; } else { print ("<name>.vdb failed to deploy"; }
connectAsAdmin(); // first deploy the JDBC jar file for Oracle deploy("ojdbc6.jar"); props = new Properties(); props.setProperty("connection-url","jdbc:oracle:thin:@<host>:1521:<sid>"); props.setProperty("user-name", "scott"); props.setProperty("password", "tiger"); createDataSource("oracleDS", "ojdbc6.jar", props);
sql = connect("jdbc:teiid:<vdb>@mm://<host>:31000", "user", "user"); // select sql.eachRow("select * from sys.tables") { println "USD{it}" } // update, insert, delete sql.execute(<sql command>);
import static org.teiid.adminshell.AdminShell.*; import org.teiid.adminapi.*;