LOGON - Basic Teradata Query - 16.20

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

Purpose

Connects a user to a Teradata database.

Syntax

The following figures show both the interactive and batch mode syntax.





where the following is true:

acctid
The account identifier associated with the userid
password
The password associated with the userid
tdpid/
The identifier associated with a Teradata server
userid
A user identifier

User Logon Exit Routine

Use the User Logon Exit routine of the Call-Level Interface to make some or all elements of the logon string optional.

Usage Notes

When using BTEQ in batch mode or with input files, enter the entire LOGON command on one line. When using BTEQ interactively, do not enter a password or account id at the same time as the userid.

If a user enters a LOGON command when the user is already logged on, BTEQ logs off the first session and then logs on the user for a new session using the userid, tdpid, and acctid specifications of the new LOGON command. Note that the new session inherits the control settings, such as page length and headings, from the old session.

When executing the LOGON command, the number of Teradata sessions previously determined by the SESSIONS command takes effect. It is necessary to use the SESSIONS and TDP commands before using the LOGON command for them to take effect. If more than one session is specified and one logon fails, BTEQ continues with the successful sessions.

The LOGON command is not valid in a Teradata SQL macro.

The Teradata Database imposes restrictions on the size of object names used within LOGON requests. Refer to the Teradata Database documentation for details on the limits and the ramifications for object names that contain multibyte characters.

When specifying userid as well as password values that contain the characters used to delimit the individual components of the LOGON string, such as a comma, the values must be surrounded by matched quotes.

Expired passwords cannot be reset when a TDWALLET string has been used to represent a UserID value for a LOGON command.

When resetting an expired password using the BTEQ LOGON command, the associated TDWALLET entry for the password does not get updated.

Using the Optional tdpid Identifier

Use the optional tdpid identifier to specify a particular Teradata Database. See the system or site administrator for the identifier associated with the Teradata Database that is planned for use. If the user does not specify a tdpid and the site administrator has not updated the System Parameter Block, the default identifier is DBC for workstation-attached clients.

When the user specifies a tdpid in a LOGON command, that tdpid becomes the default tdpid for any future LOGON commands used within the same BTEQ executioner. To change the default tdpid, use a TDP command, or specify a new tdpid in another LOGON command.

The tdpid identifier specification, in effect, is optional if the user installation has only one TDP, if the user has previously executed a TDP command, or has chosen the default TDP. The value for the tdpid specification is not case-sensitive; the user can use either uppercase or lowercase characters.

Each time a LOGON command is used, the TDP setting gets set to the tdpid identifier value that will be used for the LOGON. If a tdpid value has not been supplied as part of the LOGON command and it is the first time the LOGON command is being used, BTEQ sets TDP to the predefined default value. If a tdpid value has not been supplied and a previous LOGON command has been issued, BTEQ uses the current TDP setting for the logon.

An IPv4 or IPv6 address can be entered as part of the tdpid. See Entering IPv4 and IPv6 Network Addresses.

User ID and Password Prompts

When using BTEQ interactively, the user can log on by entering only a tdpid, which can be followed by a semicolon or a space. If BTEQ accepts the tdpid, it clears the input area and prompts for a userid:

Userid:

The userid can be followed by either a semicolon, a comma, or a space. If BTEQ validates the logon string, it clears the input area and prompts for a password:

Password:

Supply the password associated with the userid, and specify the acctid on the same line following the password, if it is required. The password cannot include or end with a semicolon unless the entire value is enclosed in quotes. The acctid can be followed by a semicolon or a space.

On mainframe-attached systems, the user can omit the password if the site has an exit routine to add the password. On workstation-attached systems, the user cannot omit the password from the logon string.

Using the Optional acctid Identifier

The acctid string can contain special characters, but they might be interpreted differently by different output devices. A script containing special characters might have to be modified if the user routes the output to another device. Therefore, do not use special characters in the acctid string.

If the acctid has an apostrophe (single quote) character, then either use the second form of the LOGON command (the one with quotes as delimiters) or double the apostrophe character, as follows:

.LOGON 0/fml,fml, "engineering's account"

or

.LOGON 0/fml,fml,'engineering''s account'

If the acctid does not have an apostrophe, the two forms of the LOGON command are the same.

Example 1 – Account Value with LOGON

To specify the account value as part of an interactive LOGON statement, use the following syntax:

.LOGON tdpid/user,,'accountid';

(Essentially, the two consecutive commas instruct BTEQ to prompt for the password.)

Example 2 – Account Value with Password

To supply the account value as part of the password entry in an interactive LOGON, use the following syntax:

passwd, 'accountid'

If the User Enters an Incorrect Parameter

If the user enters any parameter incorrectly, the logon fails and BTEQ returns an error message. For security reasons, the error message does not state in which parameter the error occurred.

Effects of the LOGONPROMPT Command

The LOGONPROMPT command enables the user to bypass the prompts and warnings related to conventional LOGON command use. If the LOGONPROMPT setting is ON, normal LOGON command behavior results.

If the LOGONPROMPT setting is OFF:
  • BTEQ does not prompt for userid or password if the user omits these entries during logon. Therefore, if the user wants to enter a conventional logon string, the user must fully qualify the LOGON command.
  • The following commands cannot be used before a session is started:
    • = command
    • COMPILE command
    • REPEAT command
    • SQL statement

Example - Setting LOGONPROMPT to OFF

Below is an example of setting LOGONPROMPT to OFF, then logging on with a tdpid value of slugger.

.SET LOGONPROMPT OFF;
.LOGON slugger/;

BTEQ Response

*** Logon successfully completed.

Security Concerns

If the user is concerned about the security of the user password on a mainframe-attached system, the user can alter the JCL to accept the LOGON command from another data set or file under the control of ACF2 or another client-resident security system. For example:

//stepname EXEC PGM=BTQMAIN,. . .
//SYSIN    DD DSN=. . .(data set containing .LOGON command)
//         DD *

The user can then log on by simply entering the LOGON command with a valid userid and no password if the System Administrator has authorized this option.

For network-attached systems, you can use Teradata Wallet in order to keep your Teradata Database passwords private and not be exposed in scripts. For more information about using Teradata Wallet for the username or password field in the LOGON command, see Security Administration (B035-1100).

Using Connect_Wait to Decrease Wait Time

When a host group has one or more disabled nodes, AP, or COP, the time it takes to log on for BTEQ sessions increases and can become excessive when CLI initiates connections to multiple sessions through the disabled processors. Use the clispb.dat option, connect_wait, to decrease the amount of time that CLIv2 waits for responses before polling another node, AP, or COP.

See Teradata Call-Level Interface Version 2 Reference for Mainframe-Attached Systems (B035-2417) or Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems (B035-2418) for information about using the User Logon Exit routine or the connect_wait option.

Example 1 – LOGON in Batch Mode

To log on to BTEQ in batch mode as user ABC with ABC as the password (which is masked from view on the output listing), specify the LOGON command on one line:

.logon ABC,ABC

BTEQ Response

After specifying the LOGON command, BTEQ displays:

*** Logon successfully completed.
*** Total elapsed time was 3 seconds.

Example 2 – LOGON Interactively

To log on to BTEQ interactively as user ABC, specify the following LOGON command:

.logon ABC

BTEQ Response

After specifying the first part of the LOGON command, BTEQ prompts for the password:

Password:

After supplying the password, which is not displayed, BTEQ displays:

*** Logon successfully completed.
*** Total elapsed time was 3 seconds.

Example 3 – Using Double Quotes to Avoid Single Quote Stripping

When specifying the account value for a LOGON command included as part of invoking BTEQ using a UNIX or Linux shell, surrounding quotes around that value may be stripped, resulting in a CLI 303 error. To prevent this, add a second pair of enclosing double quotes, as shown in the following example.

bteq .LOGON tdpid/user,passwd,"'accountid'";