Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

2.4. AdminShell

2.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.

2.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. 
    connectAsAdmin("localhost", "9999", "user", "password", "conn1")
  • 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.) 
    import my.package.*;
myObject = new MyClass();
myObject.doSomething();

Note

You should disconnect from JBoss Data Virtualization before exiting AdminShell by entering the disconnect() command.

References

2.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): 
adminHelp()
If you require a specific method's definition and input parameters, use adminHelp("METHOD"): 
adminHelp("deploy")

/*
*Deploy a VDB from file
*/
void deploy(
String /* file name */)
throws AdminException
throws FileNotFoundException
Use the sqlHelp() method to list all SQL extension methods: 
sqlHelp()
If you want to obtain a specific method's definition and input parameters, use sqlHelp("METHOD").

2.4.4. AdminShell Basic Commands

Table 2.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).

2.4.5. Execute a Script in Non-interactive AdminShell

Procedure 2.3. Execute a Script in Non-interactive AdminShell

  1. Open a Command Line Terminal

  2. Run the Script

    Run the /adminshell.sh load [-Dparam=value] PATH/FILENAME command.

Note

Optional properties passed in using -D can be accessed from within the script file by using System.getProperty(). 
value = System.getProperty("param")

Important

Any connections established within the script should be disconnected before the script finishes.

2.4.6. Launch the Interactive AdminShell

Procedure 2.4. Launch the Interactive AdminShell

  1. Navigate to the adminshell Directory

    1. Open a command line terminal.
    2. Navigate to the AdminShell directory: cd /EAP_HOME/dataVirtualization/teiid-adminshell/.
  2. Unzip the AdminShell: unzip teiid-VERSION-adminshell-dist.zip

  3. Launch the adminshell Script

    Run the ./adminshell.sh command.
    The log output is as follows: 
    ======================================================================

  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>
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.

2.4.7. Helpful Tips for the Interactive AdminShell

Table 2.2. Interactive AdminShell Commands

Command Description
↑ ('up arrow')
Displays previous 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.

2.4.8. Save a Script in Interactive AdminShell

Procedure 2.5. Save a Script in Interactive AdminShell

  1. Open the Interactive AdminShell

    1. Open a command line terminal.
    2. Navigate to /EAP_HOME/dataVirtualization/teiid-adminshell-VERSION/.
    3. Run the ./adminshell.sh command.

  2. Start Recording to a File

    Enter the record start PATH/FILENAME command.

  3. Enter Desired Commands to Record

    Enter a series of commands for which you want to record the input/output.

  4. 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.

2.4.9. Execute a Script in Interactive AdminShell

Procedure 2.6. Execute a Script in Interactive AdminShell

  1. Open the Interactive AdminShell

    1. Open a command line terminal.
    2. Navigate to /EAP_HOME/dataVirtualization/teiid-adminshell-VERSION/.
    3. Run the ./adminshell.sh command.

  2. 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.

2.4.10. Launch the AdminShell GUI

Procedure 2.7. Launch the AdminShell GUI

  1. Navigate to the adminshell Directory

    1. Open a command line terminal.
    2. Navigate to /EAP_HOME/dataVirtualization/teiid-adminshell-VERSION/.

  2. 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.

2.4.11. Save a Script in AdminShell GUI

Procedure 2.8. Save a Script in AdminShell GUI

  1. Navigate to the adminshell Directory

    1. Open a command line terminal.
    2. Navigate to /EAP_HOME/dataVirtualization/teiid-adminshell-VERSION/.

  2. 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.

  3. Save the Script

    1. Select File → Save As.
    2. Choose a location and filename for the script and click on Save.

2.4.12. Execute a Script in AdminShell GUI

Procedure 2.9. Execute a Script in AdminShell GUI

  1. Open the AdminShell GUI

    1. Open a command line terminal.
    2. Navigate to /EAP_HOME/dataVirtualization/teiid-adminshell-VERSION/.
    3. Run the ./adminshell-console.sh command.

  2. Prepare the Script

    • Type a New Script

      Type your script in the upper script window of the AdminShell GUI.

    • Load a Saved Script

      1. Select File → Open.
      2. Locate the desired script file and click on Open.
        The script will appear in the upper script window of the AdminShell GUI.

  3. Run the Script

    Select Script → Run.

2.4.13. 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: 
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
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: 
connect("URL", "USER", "PASSWORD")

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.)

2.4.14. 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: 
cName = getConnectionName();
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: 
useConnection(cName);
Example

The following example demonstrates how to use two connections and switch between them: 

// 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();

2.4.15. Example Scripts

Example 2.1. Deploying a VDB

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";
}

Example 2.2. Create a Datasource (Oracle)

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);

Example 2.3. Execute SQL Query against Teiid

sql = connect("jdbc:teiid:<vdb>@mm://<host>:31000", "user", "user");

// select
sql.eachRow("select * from sys.tables") { println "${it}" }

// update, insert, delete
sql.execute(<sql command>);

2.4.16. 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.