Advanced topics for DB2 Data Warehouse Edition users, Part 3: Command Line Interface for DB2 Data Warehouse Edition SQL Warehousing

Document options Document options requiring JavaScript are not displayed New site feature Check out our new article design and features. Tell us what you think. Rate this page Help us improve this content

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.

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.

Back to top

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:

				
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:

				
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.

				
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:

				
C:\dweclp\bin> listDWEApplication -help
Command name: listDWEApplication

Option(s): 
-format  csv

Note: The DWE CLI command names are case sensitive.

Back to top

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.

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

				
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.

				
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.

Back to top

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.

Back to top

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

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.

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.

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:

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 

Back to top

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

Back to top

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.

Back to top

Acknowledgement

The authors would like to thank Doleen Wilbur for her help in developing this article.

Back to top

Downloads

	Information about download methods

Resources

• Crash recovery utility for DB2 Data Warehouse Edition SQL Warehousing Tool: Get an overview of process instance state changes and handling unexpected behaviour.
  
  
• Best practices for choosing DB2 Data Warehouse Edition SQL Warehousing variable phases: Get an introduction to variable and attribute usage in DB2 Data Warehouse Edition SQL Warehousing.
  
  
• IBM DB2 Database for Linux, UNIX, and Windows Information Center: Learn more about DB2 Data Warehouse Edition.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Technology bookstore:Browse for books on these and other technical topics.

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

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

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top