WebSphere Message Brokers samples can be run on system with or without DB2 installed. On a non DB2 computer the samples can use a Derby database but this means that the DB2 Control Center is not installed and cannot be used to view the contents of the sample's database tables.
To view the contents of a Derby database, you must use the ij command line. A script is provided below, which can be used to run an ij command line that is correctly configured to connect to Derby databases from a WebSphere Message Brokers Command Console. Also supplied are some generic SQL statements that you can use to view the contents of the databases.
This batch file sets the required classpath and starts the Derby command line interface, ij. The batch file also makes the connection to the database specified.
To create the batch file:
@ECHO OFF SETLOCAL IF "%2"=="" ( echo Syntax: ij [DB Name] [Default Schema {UserID}] [Port Number=1527] ) ELSE ( echo To exit the ij environment, type: exit; set CLASSPATH=%MQSI_FILEPATH%\classes\db2jcc.jar;%MQSI_FILEPATH%\classes\db2jcc_license_c.jar;%MQSI_FILEPATH%\classes\derby.jar;%MQSI_FILEPATH%\classes\derbynet.jar;%MQSI_FILEPATH%\classes\derbytools.jar;%CLASSPATH% IF "%3"=="" ( java -Dij.driver=com.ibm.db2.jcc.DB2Driver -Dij.connection.%1=jdbc:derby:net://localhost:1527/%1 -Dij.user=%2 -Dij.password=APP org.apache.derby.tools.ij ) ELSE ( java -Dij.driver=com.ibm.db2.jcc.DB2Driver -Dij.connection.%1=jdbc:derby:net://localhost:%3/%1 -Dij.user=%2 -Dij.password=APP org.apache.derby.tools.ij ) ) ENDLOCAL
You can now start the ij command line in a WebSphere Message Brokers Command Console.
To start the ij command line, in a WebSphere Message Brokers Command Console, type:
mqsiij DBName SchemaName (PortNumber)
where:
When you have started the ij command line, you can enter SQL commands at the ij command prompt.
Before you can run the following SQL commands, you must have run the mqsiij.bat script from a Command Console to connect to the database with the correct schema name. This means that the Command Console is running the ij command line, in which you can then type the following SQL commands. Make sure that you end each SQL statement with a semi-colon (;).
To list all the tables in the database, type:
SELECT TABLENAME from SYS.SYSTABLES where TABLETYPE='T';
To view all of the columns of a table, type (all on one line):
SELECT DISTINCT COLUMNNAME, COLUMNNUMBER from SYS.SYSTABLES, SYS.SYSCOLUMNS where SYS.SYSCOLUMNS.REFERENCEID=SYS.SYSTABLES.TABLEID and SYS.SYSTABLES.TABLENAME='table_name' order by SYS.SYSCOLUMNS.COLUMNNUMBER;
where table_name is the name of the table.
To view all the data in a table, type:
SELECT * from table_name;
where table_name is the name of the table that contains the data that you want to view.
You can limit the maximum width of a displayed column to make the output from the SQL statements more readable. In the output, an ampersand (&) indicates that a data value has been truncated. Try setting the column width to between 15 and 30.
To limit the maximum width of a displayed column, enter the following command:
MAXIMUMDISPLAYWIDTH 30;
where 30 is the column width that you can change. The maximum column width is then set for any subsequent SQL commands that you enter. You can run this command again at any time to change the maximum column width.