WIXLIB

Best Practices forInformix DevelopersArt S. KagelASK Database Management

Best Practices for Informix DevelopersAbstract:We will explore the various ways to skin the development cat,examining several typical application module scenarios. Alongthe way, we will look at how to develop better applications thatencounter fewer processing bottlenecks and take bestadvantage of IDS's features.2

Best Practices for Informix DevelopersApplication Development LanguagesApplication PerformanceNew Informix & Informix-Related Features3

Best Practices for Informix DevelopersApplication Development LanguagesIBM Development Language Support:Informix C Software Development Kit (CSDK)Current release CSDK 4.10.xC4Embedded SQL in COBOL (ESQL/COBOL)Latest Version: 7.25.xD4IBM Universal Driver for JDBC & .NETEnterprise Generation Language (EGL)Informix Compiled 4GL (Current: 7.51)Informix 4GL Rapid Development SystemInformix SQL (Current: 7.51)4

Best Practices for Informix DevelopersApplication Development LanguagesIBM Development Language Support:Informix C Software Development Kit (CSDK) features:ESQL/C compiler driver and librariesCLI / ODBC libraryOLE DB InterfaceInformix JDBC DriverInformix .NET ProviderObject Interface for C (No longer supported)5

Best Practices for Informix DevelopersApplication Development LanguagesIBM Development Language Support:Informix SQL (ISQL) featuresPerform Forms ManagerSimple data display and maintenance formsACE Report WriterText report generator using a forms-like interfaceSQL Menu ManagerMenus to tie multiple ACE and Perform modules andother application modules together into simpleapplications6

Best Practices for Informix DevelopersApplication Development LanguagesIBM Development Language Support:Informix 4GL featuresNative code and p-machine based compiler optionsLatest release integrates to WEB/SOAP (7.50 )High level 4th Generation Language with embeddedSQLEvent driven forms control modulesEvent driven report control modules7

Best Practices for Informix DevelopersApplication Development LanguagesThird Party Informix 4GL Language Support:Three vendors support extended x4GL language:Four JsQuerixAubit 4GL – Open Source8

Best Practices for Informix DevelopersApplication Development LanguagesThird Party Informix 4GL Language Support:4Js GeneroGUI - Fully graphical XML based display systemWindows, Mac OS/X, LinuxFull WEB IntegrationAjax, HTML5Web services/components, SOAMobile ClientLanguage extensions (including embedded Java)Virtual Machine/Byte code compilerDeploy single executable on any platformSupports all major RDBMS servers (but no SQL dialect mapping)Graphical report writer (proprietary)Graphical IDE tailored to 4GL language (Proprietary - Eclipse-like)4GL Code Generator Option9

Best Practices for Informix DevelopersApplication Development LanguagesThird Party Informix 4GL Language Support:QuerixLycia II Development Workbench (Eclipse based IDE)Lycia WindowBuilder - New graphical form designerOther productivity plugins: CVS, BIRT, Jasper, etc.ESQL/C Compiler included for x4GL/ESQL-C integrationSupport for most RDBMS serversDynamic SQL dialect conversionWEB, JAVA, SOAP, REST, ADO/.NET, SAX integrationBIRT & Jasper report writers integratedLanguage extensions including full Genero extension importGraphical Thin ClientsDesktopWebMobileCreate WEB services from any 4GL function10

Best Practices for Informix DevelopersApplication Development LanguagesThird Party Informix 4GL Language Support:Aubit4GLSupport for most RDBMS serversNear complete 4GL source compatibilityCharacter, XML, GUI User InterfacesPDF report output extensionLanguage extensionsEmbedded/inline C & ESQL/C support extensionDynamic SQL dialect conversion (customizable)Open Source and free to develop and distributeCommercial support options availableMany tools includedISQL-like forms and reportsPortable dbaccess cloneMore.11

Best Practices for Informix DevelopersApplication Development LanguagesScripting Language Support:Shells and other scripting languages with sqlcmd or dbaccess Perl with DBD/DBI Informix http://search.cpan.org/ johnl/DBD-Informix-2008.0513/Informix.pm Ruby / Informixhttp://ruby-informix.rubyforge.org/ PHP Informixhttp://php.net/manual/en/book.ifx.phpGO Programming Language Python 12

Best Practices for Informix DevelopersApplication Development LanguagesMongoDB Wire Listener ProtocolSupports development stacks for MongoDB:MEAN - MongoDB, ExpressJS, AngularJS andNode.jsLibrary level APIs for: C, C , C##, Java, PHP, etc.13

Best Practices for Informix DevelopersApplication Development LanguagesREST Protocol Listener:Supports any development tools that support HTML14

Best Practices for Informix DevelopersApplication Development LanguagesOn to the meat?15

Best Practices for Informix DevelopersApplication Performance TipsINSERTs of large numbers of rows should use INSERTCURSOR:EXEC SQLDECLARE ins 1 CURSOR FORINSERT INTO mytable( col1, )VALUES (?, ?, );EXEC SQLOPEN ins 1;for (n ins 0;;) {EXEC SQL PUT ins 1 USING var1, var2, .;n ins ;.if (n ins 100) EXEC SQL FLUSH ins 1;}16

Best Practices for Informix DevelopersApplication Performance TipsINSERTs of large numbers of rows should use INSERTCURSOR:/* Number of rows successfully flushed by PUT to a full bufferor by FLUSH */num inserted sqlca.sqlerrd[2];17

Best Practices for Informix DevelopersApplication Performance TipsIncrease INSERT CURSOR performance by increasing thebuffer size:Environment:export FET BUF SIZE 32765Range: 4096 – 2 31 (Default: 4096)Global variable:extern int FetBufSize 32765;(Still limited to 16 bits – open PMR)18

Best Practices for Informix DevelopersApplication Performance TipsComplex Upsert or Merge Operations:Failed updates are cheapFailed inserts are expensive19

Best Practices for Informix DevelopersApplication Performance TipsComplex Upsert or Merge Operations:Rule of thumb:If fewer than 70% of operations are INSERTs, try UPDATE firstIf zero rows were updated then INSERT.If 70% or more are INSERTs, try INSERT firstIf duplicate key error then UPDATE.20

Best Practices for Informix DevelopersApplication Performance TipsComplex Upsert or Merge Operations:Or you can use the new MERGE statement!LOAD FROM “data.fil” INSERT INTO TEMP stager( );MERGE INTO DataTable AS dUSING stager AS sON d.key1 s.key1 AND d.key2 s.key2 WHEN MATCHED UPDATE SET d.attr1 s.attr1, d.attr2 s.attr2WHEN NOT MATCHED INSERT (key1, key2 ., attr1, attr2, )VALUES (s.key1, s.key2 , s.attr1, s.attr2, ;21

Best Practices for Informix DevelopersApplication Performance TipsAvoid lock contention using Optimistic LockingWHAT IS OPTIMISTIC LOCKING?Assume no two users will be modifying the same row at the same time.If it happens that a row which a session is modifying has been changedby another session, then deal with it at that point.Reduces lock contention.22

Best Practices for Informix DevelopersThe biggest hurdle to good multiuser applicationperformance is contention!The biggest hurdle to good data quality is poorcontention management techniques!

Best Practices for Informix DevelopersApplication Performance TipsAvoid lock contention using Optimistic LockingBasic Optimistic Locking Protocol:Fetch row without lock (no FOR UPDATE clause)Present a copy of the row for the user to modifyFetch current stored version of row with lock(with FOR UPDATE OF CURRENT clause)Validate original version against current versionIf no changes – update and commitElse rollback work and notify user24

Best Practices for Informix DevelopersApplication Performance TipsAvoid lock contention using Optimistic LockingImplementationVersion I:Every table has a DATETIME YEAR TO FRACTION(5) column*included that defaults to CURRENT and an UPDATE trigger thatmaintains the column when the row has been updated.Only have to fetch this timestamp column to validate the currentversion of the row against the original.* To get sub-second timings you MUST turn on USEOSTIME in the ONCONFIGActual resolution depends on your OS & processor architecture!25

Best Practices for Informix DevelopersApplication Performance TipsAvoid lock contention using Optimistic LockingImplementationVersion II:Add CRCOLS to every tableAdds two columns cdrserver & cdrtime which are automaticallymaintained by IDS.Add REPLCHECK column to every table26

Best Practices for Informix DevelopersApplication Performance TipsAvoid lock contention using Optimistic LockingImplementationVersion III:Add VERCOLS to every tableAdds two columns, ifx insert checksum and ifx row versionuniquely identify the version of a row. Changes in the value ofthe ifx insert checksum column will identify rows that havebeen deleted and reinserted. The ifx row version column isincremented anytime the row is updated.27

Best Practices for Informix DevelopersApplication Performance TipsAvoid lock contention using Optimistic LockingImplementationUse COMMITTED READ isolation with LAST COMMITTED optionin combination with other methods to reduce lock contention tozero.Even if there is a badly behaved application holding locks, or auser happens to query a row just as it is being updated, theuser will not be locked out or have to wait but will return thelatest committed version of the row. Optimistic lockingprotocol prevents the user from updating an out-of-dateversion.28

Best Practices for Informix DevelopersApplication Performance TipsTake advantage of Array Fetching.IDS ALWAYS sends as many rows as fit in acommunications buffer (FetBufSize 4K-2GB) in a singleoperation.ESQL/C, ODBC, 4GL, Java, and other library interfacesdeblock the communication buffer to return a single rowfrom this buffer with each CURSOR FETCH until it isdrained.Lots of overhead to have the library perform thisdeblocking for you.29

Best Practices for Informix DevelopersApplication Performance TipsArray Fetching returns the entire block of rows into yourapplication's memory space into arrays of memory one foreach column fetched.You can usually deblock the data more efficiently than theInformix libraries since you can write data specific code!* There is a bug that prevents using Array Fetch withLVARCHAR columns and UDTs (SQCODE -1831)30

Best Practices for Informix DevelopersApplication Performance TipsImplementing Array Fetching:1.Set FetBufSize or FET BUF SIZE large enough to hold a reasonablebut large number of rows2.Set FetArraySize to the number of rows that fit in the FetBufSizebuffer3.Prepare and Describe the query into an sqlda data structure4.Place a pointer to an array of memory big enough to holdFetArraySize values into the sqlda.sqlvar[col ord].sqldata for eachfield being returned5.Execute: FETCH USING (*sqlda)6.Pull the data out of the array to reference a single row31

Best Practices for Informix DevelopersApplication Performance TipsImplementing Array Fetching:FetBufSize 32765;FetArrSize FetBufSize / rowsize;EXEC SQL PREPARE my stmt FROM “SELECT tabid, tabname FROM systables”;EXEC SQL DESCRIBE my stmt INTO sqlda str;.sqlda str.sqlvar[0].sqldata malloc( FetArrSize * sizeof (int) );sqlda str.sqlvar[1].sqldata malloc( FetArrSize * 129 ); EXEC SQL DECLARE mycurs CURSOR FOR my stmt;EXEC SQL OPEN mycurs;32

Best Practices for Informix DevelopersApplication Performance TipsImplementing Array Fetching:while(1) {int *tabid (int *)sqlda.sqlvar[0].sqldata;int *tabnm[129] (char *)sqlda.sqlvar[1].sqldata;EXEC SQL FETCH mycurs USING DESCRIPTOR :sqlda str;if (sqlca.sqlcode 100) break;nrows returned sqlca.sqlerrd[2];for (row 0; row nrows returned; row ) {printf( “Tabid: %d Name: %s.\n”, tabid[row], tabnm[row] ); } }33

Best Practices for Informix DevelopersApplication Performance TipsTwo externally controlled optimizations are available34