Database Access Syntax--Connect to Remote Database
The syntax for accessing a remote database through an operating system-level command is:
command [auth_user] vnode::dbname
where:
command
Is any command used to invoke a Vector tool, such as sql.
Specifies the user name and password used for connection authentication, whether through OS authentication, DBMS authentication, or another mechanism. This syntax is useful when using DBMS authentication because it allows password entry or prompting with simplified syntax.
The auth_user can be one of the following:
+user
Prompts for the password of the user.
+user=username
Connects as the specified user and prompts for password.
+user=username,password
Connects as the specified user and password. Password is exposed on the command line.
@[username, password]
Connects as the specified user and password. Password is exposed on the command line.
Note: The @[username,password] must be specified as part of the full database name including vnode. No arguments can appear between it and the node name. For example:
sql -F8f20.10 @[me,mypassword]nodename::mydb
vnode::
Is the remote node where the database resides. The two colons are required.
The remote node can be specified as either of the following:
@host+
vnode_name
Is the virtual node name that points to the connection data and authorization data necessary to access a particular remote instance.
dbname
Is the name of the database.
Examples:
Start the terminal monitor (sql) and connect using vnode "production" to the mydb database:
sql production::mydb
Connect as the logged in OS user. Prompt for the password:
sql +user production::mydb
Connect as user fred, and prompt for the password. Fred need not be an OS user:
sql +user=fred production::mydb
Connect as user fred and enter the password on the command line. Fred need not be an OS user:
sql +user=fred,secret production::mydb
Connect as user fred and enter the password on the command line. Fred need not be an OS user.
sql '@[fred,secret]production::mydb'
Dynamic Vnode Specification--Connect to Remote Database
When connecting to a remote database (using the vnode::dbname syntax), you can specify a dynamic vnode instead of a vnode name. The dynamic vnode specification includes the connection data, user authorization, and attributes that are associated with a remote node.
Note: A dynamic vnode can be used wherever a vnode is allowed, unless otherwise stated.
A dynamic vnode specification has the following format:
@host,protocol,port[;attribute=value{;attribute=value}][[user,password]]
@host
Identifies the network name or address of the node on which the remote database is located. The @ character is required because it identifies this specification as a dynamic vnode rather than a vnode name.
protocol
Identifies the network protocol to be used by the local node to connect to the remote node.
The valid value is: tcp_ip.
port
Identifies the listen address of the instance on the remote node.
The default Vector instances use the following symbolic ports:
Vector: VW
Vector in Hadoop: VH
attribute=value
(Optional) Is one or more additional connection, encryption, and authentication attributes for the connection. Vnode attributes are described in
Vnode Attributes Configuration.
[user,password]
(Optional) Identifies the user name and password on the remote system.
Note: The user name and password must be enclosed in brackets.
Rather than specifying [user, password] in the dynamic vnode specification, you can use the +user username syntax before the dynamic vnode specification. The +user flag prompts for the password, which protects the password from exposure. For example:
sql +user=johnny @machine01,tcp_ip,VW::inventory
If the +user flag is used, you cannot also specify the [user, password] option as part of the dynamic vnode.
Examples of dynamic vnode specification:
Run the terminal monitor (sql) and connect to node hosta using protocol tcp_ip to remote Vector symbolic port VW. The login and password are Johnny and secretpwd. The remote database name is salesfact:
sql @hosta,tcp_ip,VW;[Johnny,secretpwd]::salesfact
Establish a direct connection by using the connection_type attribute:
sql @hosta,tcp_ip,VW;connection_type=direct[Johnny,secretpwd]::salesfact
Start the terminal monitor, connect to node myhost01 as user johnny using TCP-IP protocol (the default), and prompt for the password:
sql +user=johnny @myhost01,VW::salesfact