Informix User Forum 2005 Moving Forward With Informix

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 jleffler@us. ibm. com 9 th December 2005 2

Agenda • Origins of SQLCMD • Basic Use – SQL – History – Controls • SQLUNLOAD • SQLRELOAD • Build and Install 9 th 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. 9 th 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. 9 th 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]: 9 th 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 9 th December 2005 SQLCMD – A Better DB-Access? 7

Connections • CONNECT statement: – CONNECT TO ‘dbase@server’ AS ‘conn 1’ 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. 9 th 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 ‘|’ – $DBDELIMITER or pipe – Record delimiter – Newline – Escape – Backslash – Quote – Double quote 9 th December 2005 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. 9 th December 2005 SQLCMD – A Better DB-Access? 10

Alternative Output Formats • Fixed – Fixed width fields. – No spaces between fields. • Fix. Sep New! – Fixed width fields with separator – No separator after last field • Fix. Del New! – Fixed width fields with delimiter – Including after last field • Expect ‘format’ to change in future – format variable delimited; – format fixed separated; 9 th 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 ‘rn’; 9 th 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. 9 th 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 Another. Table; • No transaction management. – Roll your own with • BEGIN WORK • COMMIT WORK – Or use RELOAD instead. 9 th 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; command 9 th December 2005 SQLCMD – A Better DB-Access? – command line – built-in 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. 9 th 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 9 th 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. 9 th 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. 9 th 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. 9 th 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. 9 th 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. 9 th 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 9 th December 2005 SQLCMD – A Better DB-Access? 23

Other Built-in Commands • Benchmark – benchmark on – benchmark off • Timing – time; – clock [on|off]; – sleep 3; 9 th 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. 9 th December 2005 SQLCMD – A Better DB-Access? 25

Contexts • • • Level: 1 Input: /dev/stdin Output: /dev/stdout Error: /dev/stderr Date: mm/dd/yyyy Delimiter: '|' Escape: '\' Quote: '"' EOR: 'n' History size: 50 Query limit: 0 Transaction size: 1024 Input Base: 0 Blob. Dir: /tmp XML Record Tag: RECORD Format: <TOS> select <BOS> Heading: <TOS> off <BOS> History: <TOS> off <BOS> Continue: <TOS> off <BOS> Silence: <TOS> off <BOS> Trace: <TOS> off <BOS> Types: <TOS> off <BOS> Verbosity: <TOS> off <BOS> Benchmark: <TOS> off <BOS> 9 th December 2005 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. 9 th 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. 9 th 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 statements 9 th December 2005 – select database – read SQL from file – SQL on command line – Print column headings – Print column types – Benchmark mode – Trace executed SQLCMD – A Better DB-Access? 29

Command Line Options • Going for the full set: [a-z. A-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. 9 th 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 <rkusenet@sympatico. ca> 9 th 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. 9 th 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. 9 th 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 9 th 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 • utils 2_ak in particular. • Distributed as a gzipped tar file. – With the extension ". tgz“ – For example: sqlcmd-80. 00. tgz 9 th December 2005 SQLCMD – A Better DB-Access? 35

Compilation • Building SQLCMD requires Client. SDK. • And a C compiler. • Extract source: – tar -xzf sqlcmd-80. 00. tzg – cd sqlcmd-80. 00 –. /configure --prefix=$HOME – make install 9 th 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 Client. SDK. – 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 9 th 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 9 th December 2005 SQLCMD – A Better DB-Access? 38

Installation • Two ways of doing the install. • One-off install for a single machine. – make install prefix=/opt/sqlcmd • Places materials in: – ${prefix}/bin – ${prefix}/man 1 – ${prefix}/etc 9 th 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. 9 th 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 9 th 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. 9 th 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. 9 th December 2005 SQLCMD – A Better DB-Access? 43

Bugs? • Report bugs to jleffler@us. ibm. com • 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, INT 8, SERIAL 8 • See TODO file (and Change. Log). 9 th 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 9 th December 2005 SQLCMD – A Better DB-Access? 45

Informix User Forum 2005 Moving Forward With Informix SQLCMD A Better DB-Access? Jonathan Leffler jleffler@us. ibm. com Atlanta, Georgia December 8 -9, 2005 46