SQLCMD A Better DB-Access? - PowerPoint PPT Presentation

judson
sqlcmd a better db access n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQLCMD A Better DB-Access? PowerPoint Presentation
Download Presentation
SQLCMD A Better DB-Access?

play fullscreen
1 / 46
Download Presentation
SQLCMD A Better DB-Access?
306 Views
Download Presentation

SQLCMD A Better DB-Access?

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Informix User Forum 2005Moving Forward With Informix SQLCMDA Better DB-Access? Jonathan Leffler Sr. Technical Staff Member/IBM Atlanta, Georgia December 8-9, 2005

  2. SQLCMDA Better DB-Access Jonathan Leffler jleffler@us.ibm.com

  3. Agenda • Origins of SQLCMD • Basic Use • SQL • History • Controls • SQLUNLOAD • SQLRELOAD • Build and Install SQLCMD – A Better DB-Access?

  4. 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. SQLCMD – A Better DB-Access?

  5. 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. SQLCMD – A Better DB-Access?

  6. Basic Use of SQLCMD • Command line: • sqlcmd –d stores –e ‘select * from customers’ • Interactive mode: • sqlcmd –d stores • SQL[1022]: select * from customers; • …data printed… • SQL[1023]: Prompt includes history command number SQLCMD – A Better DB-Access?

  7. 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 SQLCMD – A Better DB-Access?

  8. 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. SQLCMD – A Better DB-Access?

  9. 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 SQLCMD – A Better DB-Access?

  10. 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. • XML • Default tag around records is ‘<record>’. • XML is not accepted as an input format – by design. Quote format is the same as CSV except the delimiter is not set by SQLCMD SQLCMD – A Better DB-Access?

  11. Alternative Output Formats • Fixed • Fixed width fields. • No spaces between fields. • FixSep • Fixed width fields with separator • No separator after last field • FixDel • Fixed width fields with delimiter • Including after last field • Expect ‘format’ to change in future • format variable delimited; • format fixed separated; New! New! SQLCMD – A Better DB-Access?

  12. 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’; No command line option (bug) SQLCMD – A Better DB-Access?

  13. 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. SQLCMD – A Better DB-Access?

  14. 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. SQLCMD – A Better DB-Access?

  15. 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 – command line transize 1024; – built-in command SQLCMD – A Better DB-Access?

  16. 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. SQLCMD – A Better DB-Access?

  17. 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 SQLCMD – A Better DB-Access?

  18. 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. SQLCMD – A Better DB-Access?

  19. 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. SQLCMD – A Better DB-Access?

  20. 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. SQLCMD – A Better DB-Access?

  21. 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. SQLCMD – A Better DB-Access?

  22. 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”; • Cancel with: • output “/dev/stdout”; • Even when system does not support those devices. Starts a new context SQLCMD – A Better DB-Access?

  23. 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 SQLCMD – A Better DB-Access?

  24. Other Built-in Commands • Benchmark • benchmark on • benchmark off • Timing • time; • clock [on|off]; • sleep 3; SQLCMD – A Better DB-Access?

  25. 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. SQLCMD – A Better DB-Access?

  26. 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 BlobDir: /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> SQLCMD – A Better DB-Access?

  27. 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. SQLCMD – A Better DB-Access?

  28. 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. SQLCMD – A Better DB-Access?

  29. Command Line Options • Try “sqlcmd -h” • Gives verbose summary of options • Basic flags: -d database – select database -f FILE – read SQL from file -e ‘SQL Statements’ – SQL on command line -H – Print column headings -T – Print column types -B – Benchmark mode -x – Trace executed statements SQLCMD – A Better DB-Access?

  30. 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. SQLCMD – A Better DB-Access?

  31. 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> SQLCMD – A Better DB-Access?

  32. 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. SQLCMD – A Better DB-Access?

  33. 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. SQLCMD – A Better DB-Access?

  34. 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 SQLCMD – A Better DB-Access?

  35. 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 SQLCMD – A Better DB-Access?

  36. 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 Default location is $INFORMIXDIR SQLCMD – A Better DB-Access?

  37. 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 SQLCMD – A Better DB-Access?

  38. 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 SQLCMD – A Better DB-Access?

  39. 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 SQLCMD – A Better DB-Access?

  40. 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. SQLCMD – A Better DB-Access?

  41. Installation • BOD can be installed on other machines • Subsequent install (possibly by root): • ./jlss install sqlcmd • ./jlss –u me –g mine –d /opt/sqlcmd \install sqlcmd • Subsequent uninstall: • /opt/sqlcmd/etc/jlss uninstall sqlcmd Default location is $INFORMIXDIR SQLCMD – A Better DB-Access?

  42. 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. SQLCMD – A Better DB-Access?

  43. 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. SQLCMD – A Better DB-Access?

  44. 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, INT8, SERIAL8 • See TODO file (and ChangeLog). SQLCMD – A Better DB-Access?

  45. Questions and Answers • http://www.iiug.org/software • The current version is 80.00 (2005-11-23) • As of 2005-12-09 Thank You SQLCMD – A Better DB-Access?

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