Db2 command line processor linux

Command line processor features

This section provides information about the command line processor features.

If a command exceeds the character limit allowed at the command prompt, a backslash (\) can be used as the line continuation character. When the command line processor encounters the line continuation character, it reads the next line and concatenates the characters contained on both lines. Alternatively, the -t option can be used to set a different line termination character.

Customizing the Command Line Processor

It is possible to customize the interactive input prompt by using the DB2_CLPPROMPT registry variable. This registry variable can be set to any text string of maximum length 100 and can contain the tokens %i, %ia, %d, %da and %n. Specific values will be substituted for these tokens at run time.

Table 1. DB2_CLPPROMPT tokens and runtime values

DB2_CLPPROMPT token	Value at run time
%ia	Authorization ID of the current instance attachment
%i	Local alias of the currently attached instance. If no instance attachment exists, the value of the DB2INSTANCE registry variable. On Windows platforms only, if the DB2INSTANCE registry variable is not set, the value of the DB2INSTDEF registry variable.
%da	Authorization ID of the current database connection
%d	Local alias of the currently connected database. If no database connection exists, the value of the DB2DBDFT registry variable.
%n	New line

• If any token has no associated value at runtime, the empty string is substituted for that token.
• The interactive input prompt will always present the authorization IDs, database names, and instance names in uppercase, so as to be consistent with the connection and attachment information displayed at the prompt.
• If the DB2_CLPPROMPT registry variable is changed within CLP interactive mode, the new value of DB2_CLPPROMPT will not take effect until CLP interactive mode has been closed and reopened.

You can specify the number of commands to be stored in the command history by using the DB2_CLPHISTSIZE registry variable. The HISTORY command lets you access the contents of the command history that you run within a CLP interactive mode session.

You can also specify the editor that is opened when you issue the EDIT command by using the DB2_CLP_EDITOR registry variable. From a CLP interactive session, the EDIT command opens an editor preloaded with a user-specified command which can then be edited and run.

Examples

If DB2_CLPPROMPT is defined as (%ia@%i, %da@%d) , the input prompt will have the following values:

• No instance attachment and no database connection. DB2INSTANCE set to DB2 . DB2DBDFT is not set.
• (Windows) No instance attachment and no database connection. DB2INSTANCE and DB2DBDFT not set. DB2INSTDEF set to DB2 .
• No instance attachment and no database connection. DB2INSTANCE set to DB2 . DB2DBDFT set to » SAMPLE «.
• Instance attachment to instance » Db2 » with authorization ID «keon14″. DB2INSTANCE set to DB2 . DB2DBDFT set to » SAMPLE «.
• Database connection to database «sample» with authorization ID «horton7». DB2INSTANCE set to DB2 . DB2DBDFT set to SAMPLE .
• Instance attachment to instance » Db2 » with authorization ID «keon14». Database connection to database «sample» with authorization ID «horton7». DB2INSTANCE set to DB2 . DB2DBDFT not set.

Using the Command Line Processor in command files

For more information about commands and command files, see the appropriate operating system manual.

Command Line Processor design

The command line processor consists of two processes: the front-end process (the Db2 command), which acts as the user interface, and the back-end process ( db2bp ), which maintains a database connection.

Maintaining database connections

Each time that db2 is invoked, a new front-end process is started. The back-end process is started by the first db2 invocation, and can be explicitly terminated with TERMINATE . All front-end processes with the same parent are serviced by a single back-end process, and therefore share a single database connection.

The front-end process and back-end processes communicate through three message queues: a request queue, an input queue, and an output queue.

The following environment variables offer a means of configuring communication between the two processes:

Table 2. Environment Variables

Variable	Minimum	Maximum	Default
DB2BQTIME	1 second	5294967295	1 second
DB2BQTRY	0 tries	5294967295	60 tries
DB2RQTIME	1 second	5294967295	5 seconds
DB2IQTIME	1 second	5294967295	5 seconds

The values of DB2BQTIME and DB2BQTRY can be increased during peak periods to optimize query time.

DB2RQTIME Once the back-end process has been started, it waits on its request queue for a request from the front-end. It also waits on the request queue between requests initiated from the command prompt.

The DB2RQTIME variable specifies the length of time the back-end process waits for a request from the front-end process. At the end of this time, if no request is present on the request queue, the back-end process checks whether the parent of the front-end process still exists, and terminates itself if it does not exist. Otherwise, it continues to wait on the request queue.

DB2IQTIME When the back-end process receives a request from the front-end process, it sends an acknowledgment to the front-end process indicating that it is ready to receive input via the input queue. The back-end process then waits on its input queue. It also waits on the input queue while a batch file (specified with the -f option) is executing, and while the user is in interactive mode.

The DB2IQTIME variable specifies the length of time the back-end process waits on the input queue for the front-end process to pass the commands. After this time has elapsed, the back-end process checks whether the front-end process is active, and returns to wait on the request queue if the front-end process no longer exists. Otherwise, the back-end process continues to wait for input from the front-end process.

To view the values of these environment variables, use LIST COMMAND OPTIONS .

The back-end environment variables inherit the values set by the front-end process at the time the back-end process is initiated. However, if the front-end environment variables are changed, the back-end process will not inherit these changes. The back-end process must first be terminated, and then restarted (by issuing the db2 command) to inherit the changed values.

An example of when the back-end process must be terminated is provided by the following scenario:

1. User A logs on, issues some CLP commands, and then logs off without issuing TERMINATE .
2. User B logs on using the same window.
3. When user B issues certain CLP commands, they fail with message DB21016 (system error).

The back-end process started by user A is still active when user B starts using the CLP, because the parent of user B’s front-end process (the operating system window from which the commands are issued) is still active. The back-end process attempts to service the new commands issued by user B; however, user B’s front-end process does not have enough authority to use the message queues of the back-end process, because it needs the authority of user A, who created that back-end process. A CLP session must end with a TERMINATE command before a user starts a new CLP session using the same operating system window. This creates a fresh back-end process for each new user, preventing authority problems, and setting the correct values of environment variables (such as DB2INSTANCE ) in the new user’s back-end process.

CLP usage notes

Commands can be entered either in uppercase or in lowercase from the command prompt. However, parameters that are case sensitive to Db2 must be entered in the exact case required. For example, the comment-string in the WITH clause of the CHANGE DATABASE COMMENT command is a case sensitive parameter.

Delimited identifiers are allowed in SQL statements.

Special characters, or metacharacters (such as $ & * ( ) ; ? \ ‘ «) are allowed within CLP commands. If they are used outside the CLP interactive mode, or the CLP batch input mode, these characters are interpreted by the operating system shell. Quotation marks or an escape character are required if the shell is not to take any special action.

Special characters vary from platform to platform. In the AIX Korn shell, the previous example could be rewritten using an escape character (\), such as \*, \>, or \’.

The command line processor represents SQL NULL values as hyphens (-). If the column is numeric, the hyphen is placed at the right of the column. If the column is not numeric, the hyphen is at the left.

To correctly display the national characters for single byte (SBCS) languages from the Db2 command line processor window, a True Type font must be selected. For example, in a Windows environment, open the command window properties notebook and select a font such as Lucinda Console. Non-printable characters such as bell (0x7) are printed without substitution, so special consideration must be given for the ASCII null character (0x0). Even though it is acceptable to have an ASCII null character (0x0) within a VARCHAR string, the display of the string ends at the first ASCII null character that is found within the string.

The command line processor does not support national language support (NLS) characters in file path names. This particularly affects commands such as IMPORT , EXPORT , and REGISTER XMLSCHEMA , where problematic file path names would most frequently be encountered.

Piping the output of a command line processor command into another command line processor command is supported. For example: db2 -x | db2 +p -tv . This support is limited only by the pipe buffer size. Pipe buffer sizes are not configurable. If the pipe buffer size is exceeded by the first command, the command line processor might hang or fail when attempting to write the output to the pipe buffer. If the second command is not a command line processor command, for example a UNIX shell command, a hang or failure will not occur due to the pipe buffer size limitation.

Источник

Db2 Basics: Setting up a Command Line to Work with Db2

I’m a command line gal, and probably always will be. I doubt a GUI will tempt me away from the command line, though I do find myself enjoying using things like Jupyter Notebook to also access the databases I support.

Does Your Database Know Who You Are?

There are a number of environmental variables that are set when you log in to the Db2 instance owner id. Some of these variables are required for working with a Db2 environment. In sandbox environments, we may do most things as the Db2 instance owner, but in any shared environment, it is critical to security to use your own ID. There are a few exceptions to this – if you’re a member of a team administering Db2, you want to use the instance ID for scheduling any scripts. No one wants to go spelunking through crontabs across the system to figure out who is running backups and things along those lines. It is also best to use the instance owner to start and stop Db2. Most other work, even that done by DBAs, can be done from their own properly privileged IDs.

Working as a consultant, I see an astonishing variety of security practices. At one end of the spectrum, I’m handed root the second I get access to the system when all I really needed was SYSMON. At the other end of a spectrum, I have actually worked on systems where I was not allowed to directly access the system – I could only tell someone else what to type via screen sharing. That is a slow and frustrating way to work.

The focus of this article is not that broad, though. The focus here is how to set up a basic Linux/UNIX command line for one ID so it can execute db2 commands and SQL.

Error Messages

There are a couple of common error messages that are immediately associated with a command line environment that is not properly set up. The first is:

This indicates that minimally your PATH variable is not set up properly, but also that there are other environment variables that should be set up as well. Sometimes a developer or system administrator tries to be clever when they get that error message, and they locate the basic db2 executable directly. However, there are still missing componnents, and even executing db2 from a location where it exists leads to this error message:

Setting Up A Command Line to Run Db2 Commands

Linux or UNIX – Sourcing the Db2 Profile

IBM provides a profile (actually two of them) that contains all the information needed to connect and run any Db2 commands you might need. This profile is stored in the sqllib directory. This directory is created in the home directory of the instance owner when the Db2 instance is created. The following lines are added by Db2 to the .profile, .bash_profile, or .bashrc when the instance is created to source the db2profile:

The path specified in these lines is going to vary depending on where your instance owner’s home directory is. Generally, your instance owner’s home directory should not be on / or on /home . Hopefully you’ve placed it somewhere else.

If you don’t know where the instance owner’s home directory is, there are several ways to find it. For any of them, you have to have access to the server in some way and know the name of the instance owner. If you don’t have this, ask your system admin or database administrator. There can easily be many Db2 instances on a server. In these examples, the instance name is represented by db2inst1 . You’ll want to replace that with your instance name.

• If you have access to the instance owning id, login and issue:
• On the server, try this:

Changes to the db2profile

It is a bit advanced for this blog article, but should you need to change anything in the db2profile, do not make the changes directly, as the db2profile is likely to be overwritten on upgrade or patching. There’s another file in the same directory called userprofile that can be used for any changes you want to make.

Windows

This is one area where Db2 on Windows is significantly different from Db2 on UNIX and Linux.

Default Command Window

I believe there’s a special place in hell for batch, so I avoid using it as much as possible, and this includes the default Windows command line. If you must use it, the only way to set it up is to find the executable for it installed when db2 is installed on Windows, and open the command line through it.

Even when I’ve written batch scripts (shudder), they have to pass things to this.

PowerShell

I am big fan of administering Db2 from a PowerShell command line on Windows. Setting up PowerShell to run Db2 commands is usually just a one-liner either just executed or added to the PowerShell $profile:

This assumes you’re working with the default Db2 instance and copy.

Interacting with Db2 on Linux and UNIX

Once you have the basic command line environment set up, there are still several ways to interact with Db2. My favorite is the first — using the standard bash/ksh command line.

Using the Standard Bash/Ksh Command Line

The top reason that I like this method is that I use vi as my command line editor, and it is incredibly easy to view and to search your command history with it. I can’t tell you how many times having commands saved in the history saved me time or allowed me to understand what happened. Most experienced DBAs I have worked with use this method as well. This approach makes it easy to also use bash/ksh to script simple loops and to pipe things to grep or more to make viewing easier. This method takes the form of simply prefixing all db2 commands and queries with «db2», usually enclosing them in double quotes to avoid trouble with shell expansion.

A simple example:

The disadvantage to this method tends to be the formatting of query output. It’s highly manual, and often done with substr and other functions in the SQL.

Using the Interactive Db2 Command Line

By simply typing ‘db2’ and hitting enter, you’re entered into an interactive Db2 command line.

I’m not the best person to extol the virtues of this method because I don’t use it.

CLPPLUS

The other method I have played with is the CLPPLUS environment. I believe this is Db2’s answer to Oracle’s SQLPLUS. On several occasions, I’ve used this environment to get output formatted nicely for a blog entry or presentation. But it’s still not my go-to choice. Check out my blog entry exploring CLPPLUS.

A Few Tips for Multi-Instance Environments

There are a few things you can do to make working with environments where you switch instances on the same server easier.

Aliases for Switching Instances

The first is to define aliases at the bash level for switching instances. One of these that I use looks like this:

These alias make it so I type one short string and the following things happen:

1. Any existing db2 command line is terminated
2. The proper db2profile is sourced
3. The current directory is changed to one I have specific to that instance
4. A connection is established to the database

Technically only #1 and #2 are required, the other two are just niceties.

Command Line Prompt

To make sure I always know what instance I’m working with, I also set my command line prompt in my .bash_profile using this code:

What this gives me at the command line is this:

In the example above, the instance name is VDBA, and it stands out so that I can quickly verify which instance I’m working with.

Summary

The command line is not the only way to work with Db2. I’m regularly using scripts in KSH/Bash, Perl, PowerShell, Python, and my recent favorite — Jupyter Notebook (Python Kernel) to interact with Db2. Scripting is a powerful way to automate the things a DBA does, easily schedule them, and ensure a greater level of consistency.

Edited on 2019-04-30 to add two missed punctuation marks, change «environment parameters» to «environment variables», and spell .bashrc correctly.

Источник