[SAP HANA] HAAN HDBSQL工具

1.  登录
2.  非交互式
3.  命令详解
4.  hdbuserstore

 

1. 登录

#登录方法1
hdbsql [<option>] -n <db_host> -i <instance_id> -u <db_user> -p <db_user_password> -d <db_name>

#登录方法2
#step 1.
 hdbsql [<option>]

#step 2.
\c -n <db_host> -i <instance_id> -u <db_user> -p <db_user_password> -d <db_name>

2.非交互式

hdbsql [<option>] -I <file>

3.命令详解:

d4cadm@red2:/usr/sap/D4C/HDB00> hdbsql

Welcome to the SAP HANA Database interactive terminal.

Type:  \h for help with commands
       \q to quit

hdbsql=> \h

 \? or \h[elp]  show help on internal slash commands
 \q[uit]        quit HDBSQL
 \c[onnect]     -i <instance number>
                -n <host>[:<port>]
                -d <database name>
                -u <user_name>
                -p <password>
                -U <user_store_key>
                connecting to the database
 \di[sconnect]  disconnecting from the database

 \mu[ltiline]   [ON|OFF] toggle the multiline mode on or off
 \a[utocommit]  [ON|OFF] switch autocommit mode on or off
 \m[ode]        [INTERNAL|SAPR3] change SQL mode
 \cl[ientinfo]  [property=value[;...]] send client info
 \ps            [ON|OFF] toggle the usage of prepared statements on or off
 \es            [ON|OFF] toggle the escape output format on or off
 \o[utput]      <filename> send all query results to file, double quotes around filename are allowed
 \i[nput]       <filename> read input from file, double quotes around filename are allowed
 \ie[ncoding]   <encoding> force input encoding, one of "ASCII", "UCS2", "UTF8"
 \hi[story]     <size> number of commands to keep in history buffer (default: 50)

 \p[rint]       print out the current query buffer (only multiline mode)
 \read          <filename> read input from file, double quotes around filename are allowed
 \r[eset]       reset (clear) the query buffer (only multiline mode)
 \e[dit]        <filename>  edit the query buffer (or file) with external editor (only multiline mode)
 \g[o]          send query buffer to server and print out the results (only multiline mode)
 ;              send query buffer to server and print out the results (only multiline mode)

 \al[ign]       [ON|OFF] toggle the aligned output on or off
 \pa[ger]       [ON|OFF] toggle page by page scroll output on or off
 \f[ieldsep]    <separator> use <separator> as the field separator
 \qto           <seconds>
 \querytimeout  <seconds> set the query timeout for executed commands to <seconds>
 \s[tatus]      print out host, database, user etc.

 \dc [PATTERN]  list columns
 \de [PATTERN]  list indices
 \dp [PATTERN]  list procedures
 \ds [NAME]     list schemas
 \dt [PATTERN]  list tables
 \du [NAME]     list users
 \dv [PATTERN]  list views
 [PATTERN] = [OWNER.][OBJECT NAME] eg. <username>.%A%

 \vd <variable name> <value>  Define a SQL Script Variable <variable name> to be replaced with <value>
 \vu <variable name> <value>  Undefine a previously defined SQL Script Variable <variable name>
 \vl list all SQL Script variables that have been defined
 \vc clear all SQL Script variables that have been defined
 \ve ON|OFF set SQL Script variable escaping with \ on or off
 \vs ON|OFF turn SQL Script variable replacement on or off
 \vp ON|OFF turn SQL Script variable prompting on or off when undefined variables are encountered

3.hdbuserstore

安装在ＨＡＮＡ客户端，用来保存ＨＡＮＡ系统肯客户端的连接．
Linux: or UNIX /usr/sap/hdbclient 
Windows:    %SystemDrive%\Program Files\sap\hdbclient
pasword rule: underscores, pyphens,periods, and (0-9,a-z,A-Z) .保存结果为大写

d4cadm@red2:/usr/sap/D4C/HDB00> hdbuserstore
Usage: hdbuserstore [options] command [arguments]

Options:
  -u <USER>       perform operation for other operating system user
  -v              verbose mode, print operations done
  -i              interactive mode, ask for missing values
  -h              this help message
  -H <HOST>       assume host name <HOST>
Commands (the command name is case insensitive):
  Help
        Print help message.
  Set <KEY> <ENV>[@<DATABASE>] <USERNAME> <PASSWORD>
        Add or update a profile entry.
        <KEY>       entry key name
        <ENV>       database location (host:port)
        <USERNAME>  user name
        <PASSWORD>  password
        <DATABASE>  database name in MDC environment
  AddFromDir <DIR>
        Add entries from a store in <DIR> without overwriting existing keys.
        <DIR>       store directory from which entries to be read
  Delete <KEY>
        Delete entry with key <KEY>.
  List [<KEY> ...]
        List entries of store. The passwords are not shown.
  ListFromDir <DIR>
        List entries from a store in <DIR>.
        <DIR>       store directory from which entries to be read
  ChangeKey
        Generate new encryption key and encrypt passwords again.

 

Action	Command	Sample
建立新用户在user store for a tenant db	hdbuserstore set <key> <host:port@tenant_db> <user_name> <password>	hdbuserstore set peter “localhost:30115@D4C” Johan Welc@me1
列出所有的可用的user keys	hdbuserstore list <key>
hdbsql 使用USER key	hdbsql -U <key>	hdbsql -U  peter
通过指定可连接的主机和端口设定灾备应用服务器	hdbuserstore set DEFAULT "<hostname_node1>:3<inst>15;...<hostname_node(n)>:3<inst>15" <sapsid> <password>	hdbuserstore set  DEFAUL “red1:33315;red2:33315;red3:33315;red4:33315” SAPDC4 <password>
为分布式tenant db 设定灾备连接Configure failover support for client running  on a distributed tenant db by specfiying a list of host/ports pairs that the client can connect to (Note:the port name is the SQL port of the name server os the system db)	hdbuserstore set  key <hostname>:<sysname_DB_SQL_port>[,<hostname2>:<sysname_DB_SQL_port>,...]@<db_name> <user> <password>	hdbuserstore set  D4X “host1:30013;host2:30013;host3:30013”@DB1 peter <password>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

更新.....