Transcript SQLCMD – A Better DBACCESS?

Informix User Forum 2005
Moving Forward With Informix
SQLCMD
A Better DB-Access?
Jonathan Leffler
Sr. Technical Staff Member/IBM
Atlanta, Georgia
December 8-9, 2005
1
SQLCMD
A Better DB-Access
Jonathan Leffler
[email protected]
9th December 2005
2
Agenda
• Origins of SQLCMD
• Basic Use
– SQL
– History
– Controls
• SQLUNLOAD
• SQLRELOAD
• Build and Install
9th December 2005
SQLCMD – A Better DB-Access?
3
Origins of SQLCMD
• Originally called RDSQL.
– Created in 1987 (oldest surviving records).
– Informix-SQL had an RDSQL option
• Instead of Query-Language.
– Informix-SQL was produced by RDS
• Relational Database Systems Inc.
• Before company went public
– Informix Software Inc.
– Renamed SQLCMD in 1992.
9th December 2005
SQLCMD – A Better DB-Access?
4
Origins of SQLCMD
• Intended as an alternative to ‘isql’.
– Before DB-Access was created.
• Designed for use in shell scripts.
– Exits with non-zero status on error.
– Careful use of standard input, output, error.
– Output layout independent of selected data.
• Designed for interactive use.
– History allows you to list, edit, rerun SQL.
9th December 2005
SQLCMD – A Better DB-Access?
5
Basic Use of SQLCMD
• Command line:
– sqlcmd –d stores –e ‘select * from customers’
• Interactive mode:
Prompt includes history
command number
– sqlcmd –d stores
– SQL[1022]: select * from customers;
– …data printed…
– SQL[1023]:
9th December 2005
SQLCMD – A Better DB-Access?
6
SQL Command Interpreter
• Executes preparable SQL commands:
– DDL
• CREATE TABLE, etc
– DML
• INSERT, DELETE, UPDATE, SELECT, …
• Results of SELECT go to output.
– Also EXECUTE PROCEDURE
– Default format is variable-width fields with
separators
9th December 2005
SQLCMD – A Better DB-Access?
7
Connections
• CONNECT statement:
– CONNECT TO ‘dbase@server’ AS ‘conn1’
USER ‘me’ PASSWORD ‘mine’ WCT;
– Accepts standard USING for PASSWORD.
– And WITH CONCURRENT TRANSACTIONS for WCT.
– Does not accept DORMANT.
• SET CONNECTION statement.
• DISCONNECT statement.
9th December 2005
SQLCMD – A Better DB-Access?
8
Alternative Output Formats
• SQLCMD formats data uniformly
– Does not vary depending on width.
– Key original design feature.
• Selectable delimiters
– Field delimiter
– Record delimiter
– Escape
– Quote
9th December 2005
– $DBDELIMITER or pipe ‘|’
– Newline
– Backslash
– Double quote
SQLCMD – A Better DB-Access?
9
Alternative Output Formats
• Select (default)
– Variable-width fields with field separators.
• Unload.
– Variable-width fields with field terminators.
• CSV
– Comma-separated variable-width fields.
– Non-numeric fields enclosed in quotes.
Quote format is the same
as CSV except the
delimiter is not set by
SQLCMD
• XML
– Default tag around records is ‘<record>’.
– XML is not accepted as an input format – by design.
9th December 2005
SQLCMD – A Better DB-Access?
10
Alternative Output Formats
• Fixed
– Fixed width fields.
– No spaces between fields.
• FixSep
New!
– Fixed width fields with separator
– No separator after last field
• FixDel
New!
– Fixed width fields with delimiter
– Including after last field
• Expect ‘format’ to change in future
– format variable delimited;
– format fixed separated;
9th December 2005
SQLCMD – A Better DB-Access?
11
Alternative Output Formats
• Command line:
– -F xml -G customer –D @ -E = \
-Q “’” –A ‘yyyy-mm-dd’
• Built-in commands:
– format xml customer;
– delim ‘@’;
– escape ‘=‘;
– quote ‘\’’;
– date ‘yyyy-mm-dd’;
– eor ‘\r\n’;
9th December 2005
SQLCMD – A Better DB-Access?
No command line option
(bug)
12
UNLOAD
• Syntax is superset of DB-Access or ISQL.
– UNLOAD [CREATE|APPEND] TO [FILE]
‘somefile’ DELIMITER ‘@’ SELECT *
FROM Table;
– UNLOAD TO PIPE ‘pipecommand’
DELIMITER ‘@’ EXECUTE PROCEDURE
mine(‘this’, ‘that’, ‘t’’other’);
• This is subject to extension in the future.
9th December 2005
SQLCMD – A Better DB-Access?
13
LOAD
• Syntax is superset of DB-Access or ISQL
– LOAD FROM [FILE|PIPE] ‘somefile’
DELIMITER ‘@’ INSERT INTO
AnotherTable;
• No transaction management.
– Roll your own with
• BEGIN WORK
• COMMIT WORK
– Or use RELOAD instead.
9th December 2005
SQLCMD – A Better DB-Access?
14
RELOAD Statement
• Syntactically similar to LOAD statement:
– RELOAD FROM ‘file.unl’ INSERT INTO
Table;
• Automatically initiates transactions
– Groups the inserts into smaller transactions.
– Unless it is started within a transaction.
• Transaction size is controllable:
-N 1024
transize 1024;
9th December 2005
– command line
– built-in command
SQLCMD – A Better DB-Access?
15
INFO Statement
• Loosely similar to the INFO statement.
– Supported by DB-Access and ISQL.
• And not the database servers.
• Interpreted by SQLCMD.
• Interrogates system catalog.
– Hairy code in places!
• Output format is the same as a SELECT.
– It is the output from a SELECT statement.
9th December 2005
SQLCMD – A Better DB-Access?
16
INFO Statement
• INFO HELP
– Lists the various available options.
• INFO TABLES
– User tables.
• INFO COLUMNS FOR sometable
• INFO DATABASES
• INFO CONNECTIONS
– Not a database query
9th December 2005
SQLCMD – A Better DB-Access?
17
History
• SQLCMD records SQL commands
– File specified by $SQLCMDLOG
• Default name: ./.sqlcmdlog
– Format is platform neutral
• Portable – including 32-bit to 64-bit.
– Can be shared by concurrent executions.
– Size is configurable.
• Default size is 50.
– File size is limited.
9th December 2005
SQLCMD – A Better DB-Access?
18
History
• History can be turned on or off.
– Defaults to on in interactive mode.
– Off otherwise.
• Some commands do not get recorded.
– INFO commands generate two commands.
• Use ‘list’ command to see previous commands
–
–
–
–
–
Synonym ‘l’.
Previous command is ‘0’.
Relative commands ‘l -10 0’.
Absolute ‘l 23 54’.
Output goes to same place as SELECT statements.
9th December 2005
SQLCMD – A Better DB-Access?
19
History
• ‘Rerun’ command runs commands again.
– Synonym ‘r’.
– Same semantics as ‘list’.
• ‘Edit’ and ‘View’ commands allow you to
edit commands.
– Synonym ‘v’.
• Beware: ‘e’ is short for ‘exit’, not ‘edit’.
– DBEDIT, VISUAL, EDITOR, “vi”.
– Commands are rerun when you exit editor.
9th December 2005
SQLCMD – A Better DB-Access?
20
Exiting SQLCMD
• Three commands to do it:
– ‘exit’ (‘e’ and ‘x’)
– ‘quit’ (‘q’)
– ‘bye’ (‘b’)
• Relic from earliest days and Informix 3.30
– The ‘informix’ program used ‘bye’ to terminate.
• EOF on standard input.
• Executed all command line options.
– And one was ‘-e’ or ‘-f’ or equivalent.
9th December 2005
SQLCMD – A Better DB-Access?
21
I/O Redirection
• You can use Unix redirection, of course:
– echo “select * from customer” |
sqlcmd -d stores -F XML
• Built-in commands:
– input “filename”;
– output “othername”;
– error “whereever”;
Starts a new context
• Cancel with:
– output “/dev/stdout”;
– Even when system does not support those devices.
9th December 2005
SQLCMD – A Better DB-Access?
22
Other Built-in Commands
• Shell escape
– ! cat /dev/null
• Printing information
– echo “This goes to stdout”
– errmsg “This goes to stderr”
• Query limit
– qlimit 32
• Only first 32 rows are shown
9th December 2005
SQLCMD – A Better DB-Access?
23
Other Built-in Commands
• Benchmark
– benchmark on
– benchmark off
• Timing
– time;
– clock [on|off];
– sleep 3;
9th December 2005
SQLCMD – A Better DB-Access?
24
Contexts
• Commands are executed in a context:
– Controls many characteristics.
• Output format, delimiters, transaction size.
– Automatically inherited by new context.
– Does not monitor SQL properties.
• Each new input file starts a new context.
– Cannot alter context of ‘calling’ file.
• Beware: edited or rerun commands
– Always run in a new context.
9th December 2005
SQLCMD – A Better DB-Access?
25
Contexts
•
•
•
•
•
•
•
•
•
•
•
•
Level:
1
Output: /dev/stdout
Date:
mm/dd/yyyy
Escape: '\\'
EOR: '\n'
Query limit:
0
Input Base: 0
XML Record Tag: RECORD
Heading: <TOS> off <BOS>
Continue: <TOS> off <BOS>
Trace:
<TOS> off <BOS>
Verbosity: <TOS> off <BOS>
9th December 2005
Input:
/dev/stdin
Error:
/dev/stderr
Delimiter: '|'
Quote: '\"'
History size: 50
Transaction size: 1024
BlobDir: /tmp
Format:
<TOS> select <BOS>
History: <TOS> off <BOS>
Silence: <TOS> off <BOS>
Types:
<TOS> off <BOS>
Benchmark: <TOS> off <BOS>
SQLCMD – A Better DB-Access?
26
Contexts
• Many attributes have a stack of values:
– Up to 10 values per context.
– Visible via the ‘context’ command.
– Heading, continue, trace, verbose, format, history,
silence, format, types, benchmark.
– Stack commands are:
• Push
• Pop
• And new values can be set (on, off)
• Other attributes have no stack.
9th December 2005
SQLCMD – A Better DB-Access?
27
Contexts
• For example, you want to drop a table,
– But it might not exist.
– And that is not an error.
– continue push; – saves current state
– continue on;
– script continues after error
– DROP TABLE x;
– continue pop; – reinstates previous state
• Could just set continue off after the DROP
– But this might not be desirable.
9th December 2005
SQLCMD – A Better DB-Access?
28
Command Line Options
• Try “sqlcmd -h”
– Gives verbose summary of options
• Basic flags:
-d database
-f FILE
-e ‘SQL Statements’
-H
-T
-B
-x
9th December 2005
– select database
– read SQL from file
– SQL on command line
– Print column headings
– Print column types
– Benchmark mode
– Trace executed statements
SQLCMD – A Better DB-Access?
29
Command Line Options
• Going for the full set: [a-zA-Z]
– Only need 11 more options
• And one (-G) is obsolescent.
– And 7 or so of those are reserved
– Long option syntax likely
• Heuristic for command line arguments
– SQL statements contain spaces.
• sqlcmd –d stores ‘info databases’
– Filenames contain no spaces.
• sqlcmd –d stores $HOME/tmp/test.sql
– If it’s wrong, use ‘-e’ or ‘-f’ explicitly.
9th December 2005
SQLCMD – A Better DB-Access?
30
Username and Password
• OK to use ‘-u username’ on command line.
• Not a good idea to use ‘-p password’.
• $SQLCMDPASSWORDS file can hide it.
– No default file name!
– Permissions should be 400 or 600.
• Unchecked – but may be checked in future!
• Based on INFOTPASS mechanism:
– Used in infotables.
– By Ravi Krishna <[email protected]>
9th December 2005
SQLCMD – A Better DB-Access?
31
Username and Password
• SQLCMDPASSWORDS file contains:
– database|username|password
• If user name given (as well as database):
– First matching entry on both yields password.
• If no user name given:
– First matching entry on database yields
username and password.
• Applies to command line connections.
– Also to the CONNECT statement.
9th December 2005
SQLCMD – A Better DB-Access?
32
SQLUNLOAD
• Simplest way to unload a table.
– The inverse of sqlreload.
• Specify database (-d) and table (-t).
– Optionally output file, format, delimiters, etc.
– Use ‘-O’ option to specify sort order
– sqlunload –d stores –t customer –O customer_num
• ‘sqlcmd –U’ forces this mode.
9th December 2005
SQLCMD – A Better DB-Access?
33
SQLRELOAD
• Simplest way to load a table.
– The inverse of sqlunload.
• Passing resemblance to DB-Load.
– But much simpler to use.
– DBLDFMT converts fixed format to load format.
• Always specify database (-d) and table (-t).
– Input file (-i) is optional.
• ‘sqlcmd –R’ forces this mode
9th December 2005
SQLCMD – A Better DB-Access?
34
Where’s the Source Code?
• The International Informix Users Group!
– http://www.iiug.org/software
– Check the software repository for other tools
• utils2_ak in particular.
• Distributed as a gzipped tar file.
– With the extension ".tgz“
– For example: sqlcmd-80.00.tgz
9th December 2005
SQLCMD – A Better DB-Access?
35
Compilation
• Building SQLCMD requires ClientSDK.
• And a C compiler.
• Extract source:
– tar -xzf sqlcmd-80.00.tzg
– cd sqlcmd-80.00
– ./configure --prefix=$HOME
– make
– make install
9th December 2005
SQLCMD – A Better DB-Access?
Default location is
$INFORMIXDIR
36
What Can Go Wrong?
• Configure can’t find a working ESQL/C.
– Install ClientSDK.
– Almost any version should be OK (5.00 up).
– Set INFORMIXDIR and PATH.
• Configure can’t find Bison, Byacc or Yacc.
– Rename connecty.y to old.connecty.y
– Rename CONNECTY.c to connecty.c
9th December 2005
SQLCMD – A Better DB-Access?
37
What Can Go Wrong?
• I don’t have the GNU readline library.
– This is not an error.
– You simply don’t get command editing.
• Except by launching an editor.
• Configure can’t find my GNU readline library.
– Assuming it is installed under /usr/gnu:
• LDFLAGS=-L/usr/gnu/lib \
CPPFLAGS=-I/usr/gnu/include \
./configure --prefix=$HOME
9th December 2005
SQLCMD – A Better DB-Access?
38
Installation
• Two ways of doing the install.
• One-off install for a single machine.
– make install
– make install prefix=/opt/sqlcmd
• Places materials in:
– ${prefix}/bin
– ${prefix}/man/man1
– ${prefix}/etc
9th December 2005
SQLCMD – A Better DB-Access?
39
Installation
• Create binary-only distribution
– Package for installation on many machines
– make BOD
– Creates sub-directory BOD
– Can be packaged up for copying.
– Requires I-Connect on target machine.
• But database server is not necessary.
9th December 2005
SQLCMD – A Better DB-Access?
40
Installation
• BOD can be installed on other machines
– Subsequent install (possibly by root):
Default location is
• ./jlss install sqlcmd
$INFORMIXDIR
• ./jlss –u me –g mine –d /opt/sqlcmd \
install sqlcmd
– Subsequent uninstall:
• /opt/sqlcmd/etc/jlss uninstall sqlcmd
9th December 2005
SQLCMD – A Better DB-Access?
41
What Else Do You Get?
• SQLUPLOAD
– Inserts or updates data in a table.
• Alpha quality code.
• INSBLOB, APPBLOB, SELBLOB, etc.
– Vignettes handling BYTE and TEXT blobs.
– Fully operational toy programs.
9th December 2005
SQLCMD – A Better DB-Access?
42
What Else Do You Get?
• Documentation on UNLOAD format.
• SQLSERVER, SQLCLIENT
– Run a single SQLCMD in background.
– Each command sent to server
– Results read back
– Uses ‘sqlcmd -M fifo’ option.
9th December 2005
SQLCMD – A Better DB-Access?
43
Bugs?
• Report bugs to [email protected]
• Not many known bugs.
– Support for extended data types is
incomplete
•
•
•
•
COLLECTIONS, ROWS, DISTINCT types
User-defined types – especially opaque types
BLOB and CLOB (BYTE and TEXT OK)
Does handle
– LVARCHAR, BOOLEAN, INT8, SERIAL8
• See TODO file (and ChangeLog).
9th December 2005
SQLCMD – A Better DB-Access?
44
Questions and Answers
• http://www.iiug.org/software
• The current version is 80.00 (2005-11-23)
– As of 2005-12-09
Thank You
9th December 2005
SQLCMD – A Better DB-Access?
45
Informix User Forum 2005
Moving Forward With Informix
SQLCMD
A Better DB-Access?
Jonathan Leffler
[email protected]
Atlanta, Georgia
December 8-9, 2005
46