Level: Intermediate Marion Behnen (mbehnen@us.ibm.com), DB2 Data Warehouse Edition SQL Warehousing Development, IBM
Patrick Titzler (ptitzler@us.ibm.com), Advisory Software Engineer, IBM
Hoi Yoo (hoiyoo@us.ibm.com), Advisory Software Engineer,
IBM
05 Apr 2007 The IBM ®
DB2® Data Warehouse Edition (DWE) Administration Console provides a
WebSphere ® Application Server (WAS) web-based user interface for common
SQL Warehousing administration tasks, such as configuration, execution, progress
monitoring and execution statistics analysis. Like other graphical user interfaces,
it is well suited to interactively walk a user through a sequence of steps to
complete a task. However, repeating the same task multiple times for a large number
of data warehouse objects (such as processes, profiles, and schedules) can be
time consuming and raises the need for batch processing capabilities.
This
article introduces an early version of the DWE Administration Console command
line interface (CLI) that extends the existing infrastructure to support execution
of those tasks in non-GUI environments, the automation of recurring tasks and
handling of large task batches.
Introduction
and topology
The DB2 Data Warehouse Edition Administration CLI
is a web service®-based application that provides the capability to deploy
and manage DWE SQL Warehouse (SQW) applications in a batch processing mode.
The DWE CLI technology preview supports DWE Administration Console version
9.1.1 (and newer versions) and is available for AIX® and Microsoft®
Windows® platforms. As depicted in Figure 1, DWE CLI
extends an existing DWE Administration Console installation by providing a server
component (1) that enables CLI clients (2) to communicate with the existing
server infrastructure.
Figure 1. DWE CLI topology
A DWE CLI installation:
- Extends the existing DWE Administration Console WAS-based server components.
- Installs a new DWE CLI client component on one or more client systems.
A CLI client running on UNIX® or Windows can access a DWE Administration Console
server running on either UNIX or Windows, as long as connectivity between the
client and host system is established.
Refer to the Administration Console Command Line Interface User’s Guide
in the Downloads section for installation requirements, instructions, and some technology preview limitations.
Using the CLI
You can issue the DWE CLI commands interactively at the CLI prompt or provide them as a parameter to the CLI. To use the CLI client interactively, enter dwe at the system prompt:
Listing 1
C:\dweclp\bin>dwe
(c) Copyright IBM Corporation 2007
Command Line Interface for DWE
For general help, type: helpDWE.
dwe => helpDWE
|
To issue a command in batch mode, specify the command as a parameter when
invoking the CLI executable. To obtain a list of supported commands, enter helpDWE
at the system prompt:
Listing 2
C:\dweclp\bin>dwe helpDWE
|
Note: The first release of the DWE CLI technology preview does not
support an input file argument to specify a file that contains dwe commands.
Listing 3
C:\dweclp\bin>dwe helpDWE
Version: dwe.v1.1
Date: January 31, 2007
dwe [dwe command]
dwe
List of DWE Commands
listDWEApplication
createDWESchedule
deleteDWEInstance
deleteDWESchedule
…
|
You can obtain help for each command by specifying "-help" as a parameter:
Listing 4
C:\dweclp\bin> listDWEApplication -help
Command name: listDWEApplication
Option(s):
-format csv
|
Note: The DWE CLI command names are case sensitive.
Session management
The DWE CLI provides two sets of commands:
- Local CLI commands, such as helpDWE, connect, and disconnect are processed by the client.
- DWE administration server commands are processed by the Administration Console server and therefore require an active connection between the CLI client and the Administration Console server.
To establish a session with the Administration Console server, issue the connect command. This command takes four parameters, identifying:
- The host system name where the application server, that has the DWE Administration Console J2EE application installed, is running
- The port number associated with the DWE Administration Console application
- The user credentials, comprised of the username and password used to authenticate the CLI user for the session
If the -hostname or -portnumber connection parameters are omitted, default values will be assigned to the hostname ("localhost") and port number ("9080"). A username (-user <user_name>) must be provided or the connect attempt will fail. If the user's password is not specified using the password <password> parameter, a prompt is displayed.
Listing 5
dwe => connect -user dweadm -password secret
DWE Connection Information
DWE Command Server = mydweadmserver.ibm.com
Server Port Number = 9080
User ID = dweadm
DWE0000I: System is connected
dwe =>
|
Listing 6
dwe => connect -hostname mydweadmserver2.ibm.com -user dweadm
Enter password: ******
DWE Connection Information
DWE Command Server = mydweadmserver2.ibm.com
Server Port Number = 9080
User ID = dweadm
DWE0000I: System is connected
dwe =>
|
The CLI user is authenticated using the operating system-based security mechanisms on the server system.
Note: The technology preview does not enforce the DWE Administration Console administrator, manager and operator authorization rule checking. Therefore, a successfully authenticated CLI user can issue every administration command, even though the user might have been granted only limited access to the DWE Administration Console web interface.
To terminate an existing connection, enter disconnect at the system prompt.
Listing 7
dwe => disconnect
DWE0000I: DISConnect command completed successfully.
DWE0000I: User name = dweadm
dwe =>
|
Note:
The quit command only exits the CLI but does not terminate an existing connection.
Session scope
When the connect command is issued, the DWE CLI technology preview stores the current session information in the users home directory. The information is removed only when the disconnect command is issued or replaced with new session information when the connect command is issued again.
Multiple instances of the CLI that were started by the same user share the same session information, regardless of whether the CLI was started in the same operating system shell or not. In other words, it is not possible for a single user to establish concurrent sessions with multiple DWE Administration Console servers.
Similarly, ending a session in one CLI instance will also end the session for all other CLI instances that the same user may have running.
Command overview
As mentioned before, DWE CLI provides commands that are processed by the CLI locally (and are not considered DWE administration related commands) and commands that are processed by the DWE Administration Console server.
The following sections provide an overview of the currently available commands. Refer to the user’s guide, which is included in the CLIo interface installation package, for detailed command descriptions and syntax diagrams.
Local Commands
Table 1. Local CLI Commands
|
Command
|
Description
| | connect | Establishes a session with the specified DWE Administration Console server | | disconnect | Terminates an existing DWE Administration Console server session | | helpDWE or ? | Displays a list of supported commands | | quit | Exits the CLI but does not terminate an existing DWE Administration Console server connection | | saveDWEConfig | Stores the current DWE Administration Console server session connection information into a file |
DWE CLI SQL Warehousing administration commands
The DWE CLI technology preview supports two general SQW administration command types,
informational commands, to query the SQW control tables, and operative commands, to create, update or remove SQW objects, such as starting an SQW process instance or removing a process schedule.
Informational Commands
Informational commands, identified by a prefix of list or get, query the SQW control tables and return high-level information in tabular form (list…) or detailed information for a specified object (get…).
Commands starting with prefix list usually do not require any parameters. However, they may optionally accept parameters for the purpose of filtering. By default, output is displayed in tabular form. To display output using comma separated values, specify parameter -csv.
Table 2. Informational CLI commands for lists of items
|
Command
|
Description
| | listDWEApplication | List deployed applications | | listDWEFailedInstance | List failed process instances | | listDWEInstance | List process instances | | listDWEProcess | List processes | | listDWEResource | List database and system resources | | listDWESchedule | List process schedules | | listDWEVariable | List variables |
Example:
listDWEApplication displays a list of data warehouse applications that have been deployed on the DWE Administration Console server to which you are currently connected.
Listing 8
dwe => listDWEApplication
Application Type Status Description
------------------------------
WH1_dec6-0934 Control Flow Enabled
WH1_dec6-093411 Control Flow Enabled
------------------------------
2 record(s)
|
Commands starting with prefix get require one or more parameters that uniquely identify the selected SQW object, such as data warehouse application, process, or process instance.
Table 3. Informational CLI commands for single items
|
Command
|
Description
| | getDWEApplication | Display application properties | | getDWEFailedInstance | Display information about a failed process instance | | getDWEInstance | Display process instance properties | | getDWEInstanceStatus | Display current status of the process instance | | getDWEProcess | Display process properties | | getDWEProfile | Display process profile | | getDWESchedule | Display process schedule properties |
Example:
getDWEApplication displays configuration information for the specified SQW data warehouse application.
Listing 9
dwe => getDWEApplication -app WH1_dec6-0934
Application = : WH1_dec6-0934
Status = : Enabled
Type = : Control Flow
Mail Provider = : mail/dwe
Home Directory = : /usr/home/dwe/dweapps/home
Log Directory = : /usr/home/dwe/dweapps/logs
Working Directory = : /usr/home/dwe/dweapps/temp
Description = : Sales fact table maintenance
|
Operative Commands
Operative commands can be used to create or remove SQW objects, such as schedules or to start, stop, or resume SQW process instances:
Table 4. Operative CLI commands for single items
|
Command
|
Description
| | createDWESchedule | Create a schedule for an SQW process | | deleteDWEInstance | Delete an SQW process instance | | deleteDWESchedule | Delete an SQW process schedule | | executeDWEFailedInstance | Restart a failed SQW process instance and block CLI until the SQW process instance finishes execution (synchronous process execution) | | executeDWEInstance | Start a process instance immediately and block CLI until the SQW process instance finishes execution (synchronous process execution) | | restartDWEFailedInstance | Restart a failed SQW process instance asynchronously | | startDWEInstance | Start an SQW process instance immediately and return control to CLI (asynchronous process execution) | | stopDWEInstance | Terminate execution of an SQW process instance |
Example:
Asynchronously start an instance of process load that is associated with data warehouse application WH1_dec6-0934. A unique process instance name shall be generated automatically.
Listing 10
dwe => startDWEInstance -app WH1_dec6-0934 -process load
|
Sample scenarios
The following scenarios illustrate how CLI can be used in batch mode to perform some administration tasks automatically using simple shell scripts. Note that the connection is established using credentials that have been previously stored (in encrypted form) to a text file using the saveDWEConfig command. In doing so, no user credentials have to be stored in the shell scripts themselves.
Start a process instance after another process has reached a final state
Listing 11
# establish a session with the server using previously stored connection
# information
dwe connect -configfile myconfig.txt
# launch process that loads data into a dimension table; block CLI until
# the process instance completes (synchronous execution)
executeDWEInstance -app DimUpdate -process DimLoad -instance diml01
# launch process that validates the data that was previously loaded; CLI
# won’t start this process unless the previous process has completed
# (this process could also be started asynchronously)
executeDWEInstance -app DimUpdate -process DimValidate -instance dimv01
# terminate current session
dwe disconnect
|
To execute process instance dimv01 only if process instance diml01 completed successfully, embed above commands into a scripting language script to determine whether the process execution status being returned by getDWEInstanceStatus is equals "Finished".
Create multiple schedules for a process
Listing 12
# establish a session with the server using previously stored connection
#information
dwe connect -configfile myconfig.txt
# create a schedule that loads data for the eastern region every Monday @ 5am
dwe createDWESchedule -app UpdateMart -process LoadRegion \
-name loadEastMon -date 2007/02/01 \
-time 05:00:00 -occurrence Monday \
-profile loadEast
# create a schedule that loads data for the eastern region every Thursday @ 3am
dwe createDWESchedule -app UpdateMart -process LoadRegion \
-name loadEastThu -date 2007/02/01 \
-time 03:00:00 -occurrence Thursday \
-profile loadEast
# create a schedule that loads data for the western region every day of the week @ 1am
dwe createDWESchedule -app UpdateMart -process LoadRegion \
-name loadWestDaily -date 2007/02/01 \
-time 01:00:00 -occurrence daily \
-profile loadWest
# terminate current session
dwe disconnect
|
Summary
The DWE CLI complements the existing DWE Administration Console by enabling the batch execution of common SQW administration tasks.
Using this tool, repetitive tasks can be easily automated, process instances executed conditionally, and progress monitored by third-party applications.
We encourage you to further explore ways to utilize the CLI to effectively manage your DWE SQW applications.
Acknowledgement
The authors would like to thank Doleen Wilbur for her help
in developing this article.
Downloads | Description | Name | Size | Download method |
|---|
| DWE Command Line Interface for Windows | dwe_admin_cmdline_for_WAS.zip | 11.5MB | HTTP |
|---|
| DWE Command Line Interface for AIX | dwe_admin_cmdline_for_WAS.tar.gz | 11.5MB | HTTP |
|---|
Resources Learn
Get products and technologies
-
Build your next development project with IBM
trial software, available for download directly from developerWorks.
Discuss
About the authors  | | | Marion Behnen is a Senior Software Engineer within the IBM Software
Group and works as a technical lead for the Data
Warehouse Edition. She has worked as an architect
and designer for the SQL Warehousing Runtime and Administration,
which included the runtime metadata and statistics. She
is a member of the DWE architecture board and
participates in future DWE product strategy. She has
more than 20 years of experience with database
application development, business reporting, data
warehousing, and business process integration. Prior to
joining IBM, she was involved with many aspects of
business process and data integration, in particular for
the manufacturing industry.
|
| | | Patrick Titzler is an Advisory Software Engineer in IBM Software Group's Business Intelligence organization and is currently working on DB2 Data Warehouse Edition. Prior to joining the DWE team, he contributed to the DB2 OLAP Server and WBI Monitor projects. Patrick holds a Master's degree in Computer Science from University of Rostock, Germany.
|
| | | Hoi Yoo is an Advisory Software Engineer in IBM Software Group's Business Intelligence organization. He has broad experience with web and database application development projects, with a focus on data warehousing. One of his main areas of interest are AI concepts and how to apply them.
|
Rate this page
| 
