Getting Started with IDS (Informix on Campus Lecture Series)

1. Getting Started with IDS(Informix on Campus Lecture Series) Pradeep Natarajan - IBM, Information Management For questions about this presentation contact: pnatara@us.ibm.com

2. Contents • Introduction to IDS • IDS – Installation and Setup • Client – Installation and Setup • Java and IDS • Informix with PHP, Ruby and Rails, and .NET • IDS Administration Basics • References

3. Introduction to IDS • What is IDS? • Informix Dynamic Server. • An online transaction processing (OLTP) relational database management system (RDBMS). • Focused on: • Performance/Scalability. • Ease of use/management. • Business continuity. • Security. • Adaptability to business applications design. • Support leading-edge development APIs. • Key industries: • Telco, Government, Retail, and Banking.

4. IDS – Installation and Setup • Getting IDS (through IBM Academic Initiative) • Join IBM Academic Initiative and sign in as a member: http://www-304.ibm.com/jct01005c/university/scholars/academicinitiative/ • Go to the Software Downloads page: http://www-304.ibm.com/jct01005c/university/scholars/downloads/index.html • Click on “Browse the Software Catalog” link. • Click on “Find by Search Text” link. • Enter search text - Informix Dynamic Server V11.50. • Select the “Images” tab, expand the search results, sort by date. • Know your platform? (Operating System and processor – 32/64bit) • Select appropriate IDS V11.70.xC3 (x can be U/T/F/H; xC3 is latest as of April 2009); at the bottom of the page select “I agree”, and download. • For comparison of editions please visit: http://www-01.ibm.com/software/data/informix/ids/ids-ed-choice/

5. IDS – Installation and Setup • Installing IDS • Extract (Windows) or untar (UNIX) the compressed file. • On UNIX and Linux: • ./ids_install -gui • On Windows: • Click on launch.exe or setup.exe • On MacOS – launch the self-extracting .dmg file. • Administrator or superuser privileges required for installation. • Welcome screen: Click on “INSTALL PRODUCTS”. (Note: The following screenshots are from IDS V11.50.TC3 in Windows XP.)

6. IDS – Installation and Setup • Select the products that you need (usually the server and the client). • Follow the Install wizard. • Custom installation allows selection of individual features. • Installation creates a user account called informix, if it does not exist. • In Windows, provide a password for informix user account. • If unsure, do not enable role separation.

7. IDS – Installation and Setup • Change or accept default values as needed. • Continue with the installation process. • In UNIX-type systems, creating a demo database server instance during installation is useful.

8. IDS – Installation and Setup • Default server configuration in Windows: • To access the server • In Windows Start  All Programs  IBM Informix Dynamic Server 11.50  ol_svr_custom • In UNIX, set the following environment variables: • INFORMIXSERVER – name of server. • INFORMIXDIR – IDS installation directory. • ONCONFIG – name of the configuration file. • INFORMIXSQLHOSTS – name of the file with connectivity info. • PATH – add $INFORMIXDIR/bin to the PATH. • In UNIX, if demo instance was created, you can execute: • Bourne shell: . $INFORMIXDIR/demo/server/profile_settings • C shell: source $INFORMIXDIR/demo/server/profile_settings.csh

9. IDS – Installation and Setup • Start the database server • On Windows, it is setup as a service and starts up automatically. From the command window enter: • starts <server name> • On UNIX-type systems, start IDS as user root or informix: $ oninit • To check the status of the server: $ onstat – IBM Informix Dynamic Server Version 11.50.UC3 -- On-Line -- Up 02:13:11 -- 78208 Kbytes (“On-Line” denotes server is ready for use) • Shut down the database server • On Windows, stop the IDS service. Start  Control Panel  Administrative Tools  Services • On UNIX-type systems, as user root or informix: $ onmode –ky

10. IDS – Installation and Setup • Creating a database • To create a known demo database “stores” dbaccessdemo7 stores –log –dbspace my_dbspace • To create your own database use dbaccess and a valid SQL statement (a command-line/screen mode client program distributed with the server) dbaccess - - create database db1 in my_dbspace with log; Ctrl + c to exit dbaccess • dbaccess can also be used to execute a SQL script (test.sql) dbaccess –e – test.sql • The “-e” echoes the command to standard output. • The “-” says do not open any database, but connect to the default server. • To enter in screen mode just type dbaccess (without any arguments or options).

11. IDS – Installation and Setup • Adding space • Logical spaces called dbspaces to store database info. • Dbspace = one or more chunks. • Chunk = physical storage like a file or a disk device. • Default in Windows: rootdbs - C:\IFMXDATA\ol_svr_custom\rootdbs_dat.000 – 200MB ol_svr_custom - C:\IFMXDATA\ol_svr_custom\ol_svr_custom_dat.000 – 200MB sbspace - C:\IFMXDATA\ol_svr_custom\sbspace_dat.000 – 200MB • To add a dbspace called my_dbspace: • Create the file C:\IFMXDATA\ol_svr_custom\my_dbspace • Allocate the file to IDS onspaces –c –d my_dbspace –p C:\IFMXDATA\ol_svr_custom\my_dbspace –o 0 –s 102400 • In UNIX-type systems, the chunk file should be owned by user informix, group informix with permissions 660.

12. Client – Installation and Setup • Two options • Informix Client-SDK • Specific to IDS. • Does not support open-source Ruby and Rails drivers. • IBM Data Server Client • Works with IDS and DB2. • Not available on MacOS. • Does not support complex types, opaque types, interval type, and boolean is seen as smallint. • Supports all drivers discussed in this presentation. • Switching to Informix Client-SDK involves minimal changes.

13. Informix Client-SDK – Installation and Setup • If Client-SDK was selected along with the server, this installation will immediately follow the server installation. • Current release – 3.70.xC4. • Includes: • Object interface for C++ • Informix ESQL/C • Global Language Support • ODBC driver • .NET provider • Visual Studio add-ins • OLE DB provider • … • JDBC driver can be installed along with the IDS server in UNIX; must be installed separately in Windows. • For development using Visual Studio, install the Visual Studio add-in.

14. Informix Client-SDK – Installation and Setup • Setup the development environment • In UNIX-type systems: • INFORMIXDIR – installation directory • PATH – add $INFORMIXDIR/bin to your PATH • Setup the IDS server info in a sqlhosts file • IDS server name – server to connect • Protocol – onsoctcp • Hostname – can also be IP address • Service – can also be port number • Ex. ol_svr_custom onsoctcp pnataraj-ibm 9089 • In Windows: • Launch setnet32.exe to setup the IDS server info Start  All Programs  IBM Informix Client-SDK 3.50 Setnet32

15. IBM Data Server Client • The IBM long-term direction. • Multiple flavors: • IBM Data Server Driver for JDBC and SQLJ • IBM Data Server Driver for ODBC and CLI • IBM Data Server Driver for ODBC, CLI, and Open Source and IBM Data Server Driver for ODBC, CLI, and .NET • IBM Data Server Runtime Client • IBM Data Server Client (includes all of the above) • Current release – 9.5. • Download from the IBM Academic Initiative site by searching for “IBM Data Server Client” or one of the other flavors from above.

16. IBM Data Server Client – Installation and Setup • Installation of IBM Data Server Client: • Extract/untar the downloaded file. • Extraction creates a directory called client • Go to the client directory. • On Windows: launch image/setup.exe • On UNIX: ./db2setup • Follow the installation wizard to finish. • Setup the environment: • In Windows, installation sets up the environment as well. • In UNIX-type systems, do the following: • Bourne shell: . $HOME/sqllib/db2profile • C Shell: source $HOME/sqllib/db2cshrc

17. Java and IDS • How to use IDS with Java? • Need a JDBC driver • IDS communication protocols: SQLI and DRDA. • JDBC drivers for IDS • IBM Data Server driver for JDBC and SQL/J: • Supports IDS and DB2 • Compliant with JDBC 3.0 and JDBC 4.0 • Long term direction • Included in the IBM Data Server Client • Informix JDBC 3.5: • Specific to IDS • Compliant with JDBC 3.0 • Supports IDS extensibility types and user-defined types • Requires Java JDK 1.4.2 or higher • Included with the IDS server and Client-SDK bundle

18. Informix JDBC – Installation • Download Informix JDBC driver: • Download from the IBM Academic Initiative site or from http://www-01.ibm.com/software/data/informix/tools/jdbc/ • Current release – JDBC 3.70.JC4 • Installation: • Extract/untar the downloaded file • Execute setup.jar (JDK 1.4.2 or higher required) java –cp setup.jar run • In Windows, setup.jar is located in the JDBC folder • Double-click to execute • Follow the installation wizard to finish • In UNIX, the Informix JDBC driver can be installed as part of the server or Client-SDK installation

19. JDBC driver - Setup • Setup • Assume Data Server driver in $JDBC_HOME and Informix JDBC driver in $INFORMIXDIR/jdbc • Add drivers to CLASSPATH environment variable • Unix: CLASSPATH=$JDBC_HOME/db2_db2driver_for_jdbc_sqlj/db2jcc.jar: $INFORMIXDIR/jdbc/lib/ifxjdbc.jar:. • Windows: set CLASSPATH=%JDBC_HOME%\db2_db2driver_for_jdbc_sqlj\db2jcc.jar: %INFORMIXDIR%\jdbc\lib\ifxjdbc.jar;. • Using the drivers • Examples programs are included with the drivers • Look for the ‘demo’ directory underneath the Client-SDK installation directory or individual driver installation directory • Refer to the book “Getting Started with IDS” by Jacques Roy

20. Informix with PHP • Supported through PHP Data Object (PDO) interface • PHP drivers for IDS: • IBM Data Server driver (PDO_IBM) • Included in IBM Data Server Client package as binary • Included in Zend Core for IBM • Open-source available at the PHP extensions community library site • Informix-specific driver (PDO_INFORMIX) • Included in Open Admin Tool (OAT) for IDS • Open-source available at the PHP extensions community library site • PHP for IDS setup • Easiest way to setup PHP environment – install OAT • OAT is open-source, PHP-based, GUI administration tool for IDS • OAT includes - IBM Informix I-Connect 3.50, Apache 2.2.4, PHP 5.2.4, PDO_INFORMIX 1.1.0 • Available at http://www.openadmintool.org

21. Screenshot from OAT

22. Informix with PHP • PDO_INFORMIX driver • Stand-alone installation of the driver requires web server with PHP setup as a prerequisite. • Download from PHP extensions community library site. • Compile source using Informix Client-SDK. • IBM Data Server driver setup • Easiest way – install Zend Core for IBM. • Available at http://www-01.ibm.com/software/data/info/zendcore/ • Includes both PDO_INFORMIX and PDO_IBM drivers. • One or both of the drivers can be enabled. • Stand-alone installation of driver is also possible: • Pre-compiled binary available in dsdriver directory, underneath the Data Server Client install directory. • Compile source using IBM Data Server Client. • Refer to “Getting Started with IDS” book.

23. IDS with Ruby and Rails • Ruby – object-oriented scripting language. • Rails – model-view-controller (MVC) for web apps. • Drivers for IDS • IBM Data Server driver: • Supported by IBM. • Binary included in IBM Data Server Client package. • Source for IBM_DB gem available from http://rubyforge.org • Ruby/Informix driver: • Not supported by IBM. • Source for Ruby/Informix gem available from http://rubyforge.org • Rails/Informix driver: • Not supported by IBM. • Source for Rails/Informix gem available from http://rubyforge.org • Refer to “Getting Started with IDS” book.

24. Informix and .NET • IDS supports .NET, and legacy (ODBC and OLE DB) drivers: • ADO.NET managed provider bridge to ODBC or OLE DB driver. • .NET provider access the database directly without bridging to code outside the .NET framework. • Drivers for .NET: • IBM Data Server .Net provider. • Informix .NET provider. • IBM Data Server .Net provider • Part of the IBM Data Server Client package. • Includes add-in for Visual Studio 2008 (supports 2005 and 2003) • IBM Database Add-In for Visual Studio includes: • IBM Database Project types. • Server Explorer. • IDS SPL Editor.

25. Informix and .NET • Informix .NET provider • Included with Informix Client-SDK package and the server package. • Supports Visual Studio 2003 & 2005 (2008 not supported). • Supported programming interfaces: • Visual Basic .NET • Visual C# .NET • Visual J# .NET • ASP.NET • Main IBM Informix .NET Provider classes: • IfxConnection • IfxCommand • IfxDataReader • IfxTransaction • IfxDataAdapter

26. Informix and MATLAB • Use MATLAB to access data from Informix database. • Load data from a database into MATLAB array. • MATLAB can connect to ODBC or JDBC data sources. • Use Database Toolbox. • Setup MATLAB to use the appropriate ODBC or JDBC driver for Informix. • MATLAB references: • http://www.mathworks.com/access/helpdesk/help/toolbox/database/ug/bre07n6.html • http://www.mathworks.com/access/helpdesk/help/toolbox/database/gs/braiey2-1.html

27. IDS Administration Basics • Open Admin Tool (OAT) for IDS • OAT is open-source, PHP-based, GUI administration tool for IDS. • Available at http://www.openadmintool.org • Easy way to handle most administrative tasks. • Prominent features: • Health center. • Logs management. • Task scheduler. • Space administration. • Server administration. • Enterprise replication. • Performance analysis. • SQL toolbox. • Help.

28. IDS Administration Basics • Starting and stopping IDS • In Windows, start/stop through the services window. • In UNIX: • oninit • onmode –ky • Check the server • Current status: • onstat – • Check disk space utilization: • onstat –d • Lists information about chunks (a piece of physical storage – a device or a file) and dbspaces (logical grouping of physical storage) • To add dbspaces or chunk: • onspaces

29. IDS Administration Basics • Understanding Logs • Logging allows multiple statements in a transaction. • Logging will preserve database consistency. • Out of log space = server hang: • Add more log space • Use OAT, or • onparams –a –d <dbspace> -s <size in KB> • Backup the logs and free them for reuse: • ontape • Loading and Unloading Data • Through SQL statements: • UNLOAD TO '/tmp/file.out' DELIMITER '|' SELECT * FROM items; • LOAD FROM '/tmp/items.out' DELIMITER '|' INSERT INTO items; • Execute SQL statements using dbaccess client.

30. Resources • “Getting Started with IDS”, Jacques Roy. • The Online IDS Information Center • http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp • One-stop shop for IDS product documentation • Supports book marking favorite topics, narrowing the scope to refine searches, printing subsets of topics • IBM Informix Library • http://www-01.ibm.com/software/data/informix/pubs/library/ • This is the library for the entire Informix family of products • Most of the documentation is available as .pdf files • Informix Examples Exchange • http://www.ibm.com/developerworks/exchange/dw_category.jspa?categoryID=1034 • A new developerWorks Web site where you can find, rate, and submit code examples for IBM Informix products

31. Resources • IBM Informix DeveloperWorks Technical Articles • http://www.ibm.com/developerworks/db2/products/informix/index.html • Premium technical resource site for DBAs and developers • Features explained with examples/sample code • Contributions from IBM experts as well as customers • IBM DeveloperWorks IDS Blogs • http://www-128.ibm.com/developerworks/blogs/page/roundrep (IDS Replication) • http://www-128.ibm.com/developerworks/blogs/page/gbowerman (IDS Application Development) • http://www-128.ibm.com/developerworks/blogs/page/idsteam (IDS Experts Blog) • IBM Redbooks • http://www.redbooks.ibm.com/cgi-bin/searchsite.cgi?query=informix • Publications developed by the IBM International Technical Support Organization (ITSO)

32. Summary • What is IDS? • IDS – Installation and Setup • Client – Installation and Setup • Java and IDS • Informix with PHP • Informix with Ruby and Rails • Informix and .NET • Informix and MATLAB • IDS Administration Basics