How To Use An Informix System With A Computer System (For A Dba)

Open Source and Third Party

Software Supporting Informix

Ognjen Orel

Who am I?

• Project manager, DBA, developer… • 16 years Informix experience

• IBM Champion 2013-2015

• Working for University Of Zagreb’s Computing Centre

• Academic Information Systems division

• Design, development and maintenance of several nation-wide information systems covering higher

How Cool is Informix?

• Greatest database on Earth • Zero-admin, just set it up

• Executes queries, handles security, replication, cooks, cleans around the house, …

Ok, but really?

• There are many ways Informix could be used

OLTP – OLAP

Standalone server – replication

Business logic inside the DB – Business logic in the apps

User’s roles and permissions hanled by the DB – handled in the apps

Ok, but really?

• There is a need for different additional

software to accommodate various day-to-day needs

• I will show some of it, from a perspecitve of information system development and

Information System

• Consists of

– Single or multiple databases (maybe different DBMSs)

– Number of end-user apps – desktop, web, mobile – Number of back-end apps

– Set of business rules

– Sets of users with different roles

Additional software

DBA Stuff

• Monitoring • Alerting

• Maintenance • Management

One for you

• Q: How do you know when your instance is down?

One for you

• Q: How do you know when your instance is down?

– What/who sends the alert?

• Software • User

Shell scripts & cron

• Simple monitoring and alerting • Collect various data of interest

– Point-in-time data: Server load, number of users, ready threads etc

– Aggregated data: daily checkpoints stats, tx stats, etc (e.g. from sysadmin database)

SQL API cron

• Simple monitoring and maybe alerting

• Collect various data of interest directly from database

• insert into sysadmin:ph_task values (....) • Or configure it through OpenAdmin Tool

Command line utilities

• Plenty of those in the IIUG Software Repository

• http://www.iiug.org/software/

BusyBee

• Developed by Eric Herber long time ago • Now open-sourced and hosted on

SourceForge

• Python based scripts for monitoring various parameters

– Online status, chunk status, extents, log entries, memory, cache, …

BusyBee

• Configurable

– Tresholds for sending different kinds of alerts – Sending e-mail alerts

Zabbix

• Open source monitoring software

• Supports various types of agents to collect data from

• Monitor hardware

– Servers, UPSs, network appliances, A/Cs…)

• Monitor software

Zabbix

• Use with Informix in two ways:

– Store monitoring data

http://www.iiug.org/opensource/

– Monitor Informix instance (template by Cesar I. Martins)

OpenAdmin Tool

• Should be a household name for every DBA • Provides monitoring and maintenance of

multiple instances • Health advisor

One for you

• Q: when you migrate to a newer version (i.e. upgrade your instance), what do you do with your onconfig file?

– Use it as it is with a new version?

migrateOnconfig

• You want your onconfig to match the current Informix version, instead of adding a bunch of new parameters to the end

• Will produce a new file with .std structure and comments, but with original values and

additional comments • Uses Groovy language

migrateOnconfig

groovy migrateOnconfig.groovy \

-i $INFORMIXDIR/etc/$ONCONFIG \

-s /opt/IBM/informix1210FC5/etc/onconfig.std

Other management and monitoring

products

• There are some great Informix–based

products for DBAs and developers , with commercial and free licenses

Developer Tools

• SQL/SPL Tools • ORM

• Application Development Frameworks • Testing

Another one for you

SQL Editor

• Developed by Informix in the 1990’s • Pros:

– Small footprint – Keyboard-centric

SQL Editor

• Cons

– Outdated

– Only for Windows – Single level undo

IBM Data Studio

• Based on Eclipse IDE • Heavy footprint

• Complicated for simple use-cases

Some other SQL tools

• Plenty to choose from

• Some are integrated in IDEs • Some are too smart

ifmx-sql-editor

• An improved copy of original SQL Editor • Wrote it in Groovy

• Light client

• Keyboard centric, same shortcuts • Multilevel undo/redo

ifmx-sql-editor

• Runs on JVM

• Runs on every OS that supports Java • Download files and execute main jar

Object-Relational Mapping (ORM)

• Matching object-oriented concepts to a relational model

• Data types

• Structure differences • Transaction handling

Java Object-Relational Mapping

Frameworks

• Hibernate (http://hibernate.org/)

• MyBatis (http://mybatis.org/)

• Java Database Objects (JDO)

Application Frameworks

• Fundamental tools for enterprise class applications

• Covering the whole development process or just a part of it

– Spring Framework (http://spring.io/)

RIA Development Frameworks

• RIA = Rich Internet Application

• Considering CRUD based applications • Good RIA Development Framework

– Works with various RDBMSs – Takes care of ORM

– Simplifies client-server communication

RIA Development Frameworks

• OpenXava (http://www.openxava.org/)

• Vaadin (http://vaadin.com/)

SmartGWT

• Uses GWT (Google Web Toolkit) as a front-end components

• Encapsulates and extends GWT components in Java

• All client-server interaction (in the code) done via data source definitions (no SQL needed) • Did not support Informix until recently

Testing the Code

• Unit testing with JUnit

• Test Java classes and methods.. and SQL/SPL code

• Run within continuous integration tool or by hand

• A separate, prepared database for these tests • Each test rollbacks the transaction at the end

Testing the Code

• A simple test for a stored procedure

@Test(timeout=20000)

public void izvanredni() throws SQLException {

SmallintField sifTemeljFin =

new SmallintField( databaseToolkit ); DbStatement.executeSelectIntoAndRelease(

"execute procedure izracunP( 1100190 )" , new WrapperField[]{sifTemeljFin}

, databaseToolkit , connection);

And another one for you

• Q: Do you know how much torture can your database engine / db server stand?

– Concurent queries

– Users performing daily work

– Users performing complex queries

Stress Tests

• Find out how much torture can your database stand

• Apache JMeter is a great tool for stress-testing

Stress Tests

• Idea:

– Prepare a separate database for stress test (or better, a separate environment)

– Back it up

– Prepare enough data that tests can use (csv files) – Run the tests from one or more

Project Management Tools

• Minimum set of features required

– Workflow tracking – Bug tracking

– Reporting – Wiki

Issue-tracking software

• Should provide workflow management and reporting • Bugzilla

• Teamwork • YouTrack • JIRA

• …

• Those built in Java upon Hibernate framework could use Informix

Wikis

• Should provide flexible interface, ease of collaboration, permissions, …

• MediaWiki • XWiki

• Confluence • …

Instead of a conclusion

• The acceptance of the DB depends, among other things, on the availability of the tools and the amount of the software supporting it • We all want Informix to grow

What can we, as a community, do?

• Share the knowledge • Share our code

– Scripts, tools, software, …

– No matter how small or insignificant, there is always a novice out there who’ll appreciate it

– Participate in other open-source projects in order to make them Informix-friendly

Questions?