Technical notes on Informix Dynamic Server by a worldwide team of Development and Technical Support engineers. The postings on this site do not represent the positions, strategies or opinions of IBM.
Tuesday June 30, 2009
OpenAdmin Tool Version 2.25 Available Now!
What's new in OAT 2.25?
Schema Manager Plug-in version 1.0
Introducing a new plug-in for OAT: the
Schema Manager. Use this plug-in
for a dashboard-like of view databases and tables on your IDS database
server. For databases, see stored procedures, functions, sequences,
UDTs, privileges, DataBlades, aggregetes, casts, and opclasses from one
single view. For tables, views, and synonyms, the Schema Manager shows
you information about its columns, indexes, references, constraints,
privileges, UDTs, fragments, and triggers. The Schema Manager also
provides you with ability to drill-down on any of its pods for more
detailed information.
A demo of the new Schema Manager funcationality is
available here.
More enhancements to this plug-in will be coming soon, so stay
tuned!
Enterprise Replication Plug-in version 2.2
The Enterprise Replication plug-in administration
capability has been expanded to allow the modification of
objects:
Modify a replicate set by adding or deleting replicates
Modify a replicate set by changing the replication frequency for the replicates in the set
For additional information on OpenAdmin Tool for IDS,
including feature details, screenshots and demos, go to www.openadmintool.org.
To post comments or questions about OAT, use the IIUG OAT forum.
Overwhelmed by all of the onstat options? Need to find which commands work with ER and which work with HDR? With generous help from Tech Support, the ID team has produced an onstat Quick Reference Card and an onstat portal that lists onstat utility commands by functional category. Categories include:
Archive Information Options
Cache Information Options
Debugging Options
Enterprise Replication Options
High-Availability Replication Options
I/O Options
Locks and Latches Options
Logs Options
Memory Options
Other useful onstat Utility Options
Network Options
Performance Checks (First Tier)
Performance Checks (Second Tier)
Table Options
Thread Options
User/Session Options
Virtual Processor Options
Waiting Options
Each category represents a different IDS feature for which onstat commands provide troubleshooting and performance information. Commands that appear in bold typeface are especially useful for providing troubleshooting information. Certain onstat commands are specific to one category while others provide general information and are listed in more than one category. The card is designed as a double-sided trifold brochure and looks best when printed in color.
This article will provide tips on the easiest ways to upgrade your OpenAdmin Tool installation.
But first, we must start with describing the two different ways to install OAT:
Automated Installer
The automated OAT installer is provides an automated way to install and configure OAT and all of its prerequisites. The following products are included as part of the automated installer:
OpenAdmin Tool for IDS (and the ER plug-in)
IBM Informix I-Connect 3.50
Apache 2.2.4
PHP 5.2.4
PDO_INFORMIX 1.2.6
What platforms? The automated installer is only available on the following platforms: Windows, Linux, MacOS.
When to use it? The automated installer is really intended for first time installations. It's purpose is to drastically simplify the initial step of installing and configuring OAT's prerequisites. The automated installer is also limited to those who do not require custom web server configurations. And those installing on Windows, Linux, or MacOS platforms.
Manual Installation
The manual installation is when you install and configure your own Apache, PHP, PDO_INFORMIX, and Informix CSDK or I-Connect prior to installing OAT. Under this installation method, OAT comes as a zip or tar file that you can just extract into the webserver's document root directory after all prerequisites have been installed and configured.
What platforms? Any platform for which you can get OAT's prerequisite's installed. Although, for some platforms this can be very tricky.
When to use it? You are installing on a platform other than Windows, Linux, or MacOS. Or you have special web server configuration needs that are not supported by the Apache web server shipped with the automated installer. Or you already have a web server/PHP setup, which is exactly your situation if you are upgrading OAT!
Upgrading OAT
So now that we are clear on the initial installation choices, we can finally get to talking about upgrading OAT.
The Automated Installer only supports full installations. It does not support installing individual components (e.g. OAT only), or upgrading components of the installation. Therefore using the automated installer for upgrading OAT only makes sense if you want a completely new installation of the webserver, PHP, and OAT. (Note: on Windows, this will require completely removing your old installation first.)
Therefore, the recommended method for upgrading OAT is to use the manual install package. This makes sense because you already have the webserver, PHP, PDO_INFORMIX, and I-Connect installed and configured. When upgrading, all you need is the new version of the OAT product -- which is just a set of php files. To upgrade, download the OAT zip or tar file and extract it into your web servers document root directory and then visit the OAT install URL (http://<servername>/<oat_directory>/install) to upgrade.
Now when you choose the manual upgrade of just extracting the new version of OAT into your webserver's document root directory, you have two choices:
Extract over the existing installation of OAT
This method is fast and easy. You just extract the newest OAT tar or zip file over the existing OAT directory, visit the OAT install URL (http://<servername>/<oat_directory>/install) to upgrade your OAT connections database, and you're on your way. The disadvantage is that you lose your previous OAT installtion, including any custom changes you may have made to the files shipped as part of the OAT product.
Extract into a new directory in your webserver's document root (Recommended)
This allows you to preserve your OAT existing installation should you ever want to go back to it. But what about preserving the connection information from your previous OAT installation? Again there are two options:
You can make a copy of your previous connections.db (see the OAT Admin Config page for its location) and point your new installation of OAT to the copy. Then when you visit the the OAT install URL (http://<servername>/<oat_directory>/install) of the new installation, it will automatically upgrade the connections.db to the latest version and preserve all of your connection information. But since the OAT connections.db stores OAT plug-in information in addition to IDS server connection information, when you use this method, you will either have to install the same plug-ins into the new version of OAT or copy the OAT plugin directory from your previous installation into the new version of OAT.
The other option for preserving IDS server connection information when upgrading OAT is to use the new OAT 2.24 import/export connections feature. This allows you to export OAT's IDS connection information into an XML file and then import it into another installation of OAT. Of course, here too, if you want the same plugins as your previous installation of OAT, you'll still need to use the Plug-in Manager to install the plug-ins in the new OAT installation.
For additional information on OpenAdmin Tool for IDS, including feature details, screenshots, demos, and the download link, go to www.openadmintool.org. Any questions about OAT or its installation/upgrade can be posted on the IIUG OAT forum.
It's been five months since the last release of OAT, but hopefully it's been worth the wait. OpenAdmin Tool version 2.24 is now here to make IDS administration even easier!
What's new in OAT 2.24?
Compression
The Storage Optimization feature is new to IDS in 11.50.xC4. And OAT 2.24 provides the graphical interface that makes saving disk space easy.
Compress, uncompress, repack, and shrink operations are all available through OAT. Save disk space by compressing tables and table fragments, consolidate free space in tables and fragments through a repack operation, and return free space to the dbspace through the shrink operation. OAT also helps you to decide which tables or fragments to compress by graphically showing you the estimated the amount of space that compression will save.
SQL Trace for Historical Data
The popular SQL Explorer feature just keeps getting better. In OAT 2.24, the SQL Explorer now allows you to permanently store SQL Trace data in the sysadmin database. Then you can use the SQL Explorer to perform a query drill-down to gather statistical information not only on live SQL statements, but historical SQL as well!
Also new to the SQL Explorer, a Query Optimization Workbench which allows you to create and save external directives to modify a query's behavior.
Query By Example
Query By Example is a new addition to OAT's SQL Toolbox. Query by Example is a easy-to-use CRUD interface that can be used to perform these standard SQL operations on a table: query, insert, update, and delete.
Import/Export Connections
OAT now supports importing and exporting IDS connection information using XML. This feature, available on the OAT administration page, will save you time in setting up OAT as it allows for the ability to export OpenAdmin Tool connection information to an XML file and import it into another instance of OAT.
Enterprise Replication Plug-in version 2.1
The Enterprise Replication plug-in has been building with each release. Version 2.1 of the ER plugin marks a major stride forward - with its support for ER administration and setup. The ER plugin can now be used to do remote graphical setup, administration, and monitoring of Enterprise Replication - on the domain level, on the node level, and on the replication level.
The new ER setup and administration support in OAT includes the ability to:
Define ER servers
Define replicates and replicate sets
Define and realize templates
Start, stop, suspend, and resume replicates and replicate sets
Check replicates and replicate sets for data inconsistencies
Repair data inconsistencies in replicates and replicate sets
Synchronize data to repair inconsistencies in replicates and replicate sets
Repair failed transactions by using Aborted Transaction Spooling (ATS) and Row Information Spooling (RIS) files
For additional information on OpenAdmin Tool for IDS, including feature details, screenshots and demos, go to www.openadmintool.org. To post comments or questions about OAT, use the IIUG OAT forum.
Please note that IDS 9.40 [All Versions] is now "End of Support". If you are planning on remaining on 9.4, you should start discussing
with your IBM Representative regarding purchasing a service extension else please consider upgrading to the current IDS release.
The upgrade portal is a good resource for your upgrade plans and is a definite recommendation. If you need any additional assistance or have a quick question, please feel free to email getIDS11@us.ibm.com.
The IIUG
conference has now come to an end. Day 3 ended strong as well and I heard very
good comments on the afternoon sessions of Day 3 - It is always good to see
folks attend the sessions right through to the last one. The morning started
with a Q&A session with Alyse, Bernie, Rachel, Al and Jerry. There were
some very good questions that has been at the back of every users mind around
pricing and some recent moves within support specifically related to
entitlement. We received a lot of good feedback as well and I am sure there
will be some follow-up to these.Rachel
talked to our community investments and how there were several avenues to
pursue to participate within the community and how IBM could assist in these
efforts. I hope we get to grow our user community even more and we have a
stronger presence going forward.
The Day 3
sessions were equally good and there were several good sessions that occurred
that day. Some sessions of note were the compression and the disaster recovery
sessions and these were great hits among the audience. If you were not able to
attend any session and wanted to get your hands on the material, it will be
available soon in an electronic format.
BTW,
congratulations to Spokey on winning the fastest DBA challenge - 54 seconds….{Thanks to Spokey for the clarification}
So in
summary- IIUG 2009 was yet another
successful conference. We had great announcements, great speakers, great
sessions and above all a great conference planning committee.. See you all
again in 2010 hopefully back in Kansas City!
Combining
Day 1 and Day 2 activities. It has been a busy 2 days with some great
announcements. We started off with Arvind announcing xC4 and most importantly
our storage optimization i.e. compression feature and warehouse feature.
This site
features a ROI tool and a white paper that is a must read for all. It clearly
articulates to the value proposition of compression and how easy it is to setup
and use. The OAT sessions and the demos showcased this feature and ease of use
very well and these sessions were amongst the popular ones.
The
warehouse announcement is another exciting announcement that went out and this
includes tooling that greatly simplify your warehouse design and deployment and
aids in your BI decisions. The demo sessions highlighting the warehouse feature
was also a great attraction. Check out our warehouse site at: http://www-01.ibm.com/software/data/informix/warehouse/
The site
features a white paper that is a great read…. We had another great keynote with
Jerry and Kevin who walked us through the Informix roadmap. I am sure this
session gave everyone an idea of where Informix is heading and how each of you
can help with driving key features within the product as well. The rest of the
day as usual had some great sessions and I just did not want to highlight one
over the other. Let me just state that there were almost 25 great sessions that
took place through the day.
On
Tuesday, Ananth gave his keynote on cloud computing and how it can be a
critical success factor in your IT plans. This is an exciting space that IBM is
in and the prospects and the potential seems endless in terms of its use
especially with its cost effectiveness and ease of deployment. Guy's blog has
some great entries on this and I would encourage each and every one to read up
on the samehttp://www.ibm.com/developerworks/blogs/page/gbowerman
The
sessions again were great and the feedback has been astounding in terms of the
quality of the sessions. There is a reason why the IIUG conference is an
attractive proposition and why you should make this a yearly event on your
calendar.
Oh btw,
there is an interesting contest going on that Advanced Data Tools is sponsoring
called the Fastest DBA where the goal is to take an SQL statement that runs in
about 30 min and make it run faster. The last I checked the time to beat was
0.54 sec or somewhere around this … We definitely need more of these at future
conferences …
The
certification room was receiving its regular stream of test takers as well and
atleast the ones I saw came out smiling which is good :)
Tomorrow
is the last day of the conference and has been filled with some exciting
sessions. The key note is a Q&A session that will raise some good
discussions about marketing, support, sales and dev … will sum up the
conference tomorrow… enjoy the rest of the conference….
Well - Its
finally here - the 2009 Informix user group conference got underway in Kansas
City today. This has all the makings of another exciting conference and as usual we have
all our Informix gurus under one roof… The conference has always been a great
place to share your experiences, to learn new skills, to meet the development
and support folks who strive to give you the best possible product and service
in the industry and this year is no different. We have an impressive line up of key notes and technical sessions that will no doubt challenge you in terms of what to attend.
Today
there were several tutorial sessions from IDS workshop to Troubleshooting
series to getting ready for the IDS 11 certification exam ( You will be able
to take these certifications free of charge at the conference - a $150 value). There were several engineers from the support organization who presented these tutorials which is always special for me including John Miller, Ron Privett, Mark Jamison, Randy House, David Kolbinger, Suma Vinod, Sanjit Chakraborty and Manjula Panthagani. Hopefully these sessions were very well received and we look forward to your feedback in making this better.
My comments would not be complete without commending the people who make this conference happen. There are several of you who have been tirelessly working to make this a success and I would like to thank each and every one of you for this opportunity. Hopefully you all get to enjoy Kansas City over the next 3 days and above all have a blast at the conference and enjoy all the sessions.
Data modeling is a method used to define and analyze data requirements needed to support the business processes of an organization. Ideally, the data requirements are recorded as a conceptual model that is not platform-specific (the logical data model) with associated definitions for the entities and attributes. This logical model can serve as the ‘information hub’ to help define and enforce consistency across enterprise data. From the logical model, multiple database-specific implementations can be generated (via physical models).
InfoSphere Data Architect (IDA) - formerly called Rational Data Architect (RDA) – is a great modeling tool for any shop that includes Informix Dyanmic Server databases in its mix. Even if you don’t have a ‘data architect’, this product can help DBAs communicate with developers and also help you keep tabs on what is and isn’t “real” any more (by comparing the model of what you think you have with what your database actually does have).
I recently updated a tutorial on using IDA specifically from the perspective of using it with IDS. There are several enhancements to the model elements for IDS in IDA 7.5.1:
You can set/view extra properties for Table elements (extent size, raw table type, lock level)
Enhancements to constraints to support modes (enabled/disabled/ filtering)
Trigger enhancements suited to IDS syntax including support for INSTEAD OF triggers on views
Generate DDL enhancements for all of the above
Anyway, if you get a chance, download the IDA trial and check out the latest version of the tutorial and let me know what you think.
Rebuild sysadmin database manually without restart the IDS
There may be a situation occurs when you need to rebuild sysadmin
database without restart the IDS. For example,
previously IDS started with '$INFORMIX/etc/sysadmin/stop'
file present (or windows equivalent) and no sysadmin database exists in the instance.
Here is an example of rebuild sysadmin database manually:
cd
$INFORMIXDIR/etc/sysadmin
dbaccess -
db_create.sql
dbaccess sysadmin
db_install.sql
dbaccess sysadmin
sch_tasks.sql
dbaccess sysadmin
sch_aus.sql
dbaccess sysadmin
sch_sqlcap.sql
dbaccess sysadmin
start.sql
If you encountered any problem during above mentioned process, restarting
IDS automatically rebuild sysadmin database.
Considering '$INFORMIX/etc/sysadmin/stop' file is not exists.
IBM has
just announced The
Informix Warehouse featurewhich
consists of a powerful set of tools to build a warehouse infrastructure
platform. Here is a quote from the announcement:
From Kevin
Brown, lead architect for IBM and Jim Kobielus from Forrester Research:
"This can save weeks of effort into just a few
hours," Brown said. "In
addition, customers often did without information because of the cost of effort
to get the information. The lost opportunity cost savings is harder to
quantify, but can be significant once they use their warehouse platform for
smarter decision-making."
The
Warehouse feature includes the following components:
SQW Design Studio - An
Eclipse-based common design environment for connecting to source and target
databases, creating and reverse-engineering physical data models, and building
SQL-based data flows and control flows.
SQL Warehousing Tool -
a graphical environment that works with the Design Studio to enable you to
quickly and easily build in-database data movements and transformations into
your warehouse.
SQW Administration Console
- a web-based application for managing and monitoring the data flows that you
design using the Design Studio. The Administration Console allows you to:
Manage common
resources such as database connections and machine resources
We would appreciate your help in building a more robust, efficient, and client-focused IBM Electronic Support system by filling out this questionnaire:
Purpose: In this blog entry I will explain the appropriate steps to setting up the JDBC connectivity for IDS on Mac OS X.
Versions:
All testing for this document was prepared with the following versions of software:
-Mac OS X: 10.5.5 -Java: 1.5.0_16 or 1.6.0_07 -IDS: 11.50.FC3W2 -JCC driver: Versions 3.52.78 or 4.2.51
Dictionary:
$INFORMIXDIR = directory where your informix Informix instance is installed.
<"element"> = data that must be filled out specifically from your setup. The word "element" can be replaced by "host", "port", etc.
JCC driver = jar and zip files necessary.
(JCC driver may be obtained on: https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd Its the file identified as "IBM Data Server Driver for JDBC and SQLJ Version 3.52 (This is platform independent)").
slqlhosts file = file pointed to by INFORMIXSQLHOSTS variable in your server set up.
{JCC_JAR_FILE} = The .jar file with all the classes for the JCC driver. (Usually named db2jcc.jar or db2jcc4.jar).
Prerequisites:
*Have IDS installed with DRDA connection set up. *Have the JCC drivers.(Informix sqli driver is NOT officially supported on Mac OS X) *Install the "stores_demo" database by running the following:
"dbaccessdemo -log". Script available in the $INFORMIXDIR/bin directory. This is for testing purposes.
Steps to follow:
1-Obtain the following information about your IDS server You want to find out host, port, database , username and password: (Its going to be needed for the JDBC URL which is in the form of: jdbc:ids://<host>:<port>/<database>:user=<username>;password=<password>; )
host: IP address or name of machine running the database. This is the computer's name. You may find this in sqlhosts file. The third value of any one of the lines that have a server thats being hosted locally on your computer (or on the computer in which the server you want to connect to is in) will be the "host". Write it down.
port: The "port" on which your server is listening to. Its very important to keep in mind that the port has to be one that is using the 'drsoctcp' protocol. To verify this check the sqlhost file. Verify that the second value on one of the lines within this file is the indicated protocol. At the end of the line you will see a number. This will be the "port" that you'll need. Write it down.
database: The database is the name of the database which you will want to access. For example, if you are using the database that the script "dbaccessdemo" creates, then the "database" name would be "stores_demo".
username: The user name of the user that will be connecting to the database
password: The password of the previously mentioned user
2-Determine the appropriate version of the JCC driver to use. If you have JDK 6, you should use JCC version 4. The file related to this version is "db2jcc4.jar". If you have a previous version of the JDK you should be using JCC version 3. The related file is "db2jcc.jar". A way to determine which JCC version you are using is as follows:
Something to keep in mind about your version is that if you are using JCC version 4, then you don't need to use the Class.forName() method.
3-Now its time to create a sample app. To see a good few samples see: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.jccids.doc/com.ibm.db2.luw.apdv.java.doc/doc/cjvjdbas.htm
To test out your installation, try out the included sample java class, which is just a slightly modified version of the sample found on: http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.jccids.doc/com.ibm.db2.luw.apdv.java.doc/doc/cjvjdbas.htm
A few things to consider about the sample: You must give as a command line input the information to complete the URL. The full URL needed is in the form of: jdbc:ids://<host>:<port>/<database>:user=<username>;password=<password>; (to see how to obtain the information needed to fill out the URL, see step 1.)
The sample code already includes the "jdbc:ids" part of the URL, so you must pass the rest of the URL. For example, if the host is 'myhost', the port is '1234', the database is 'test_db', the username is 'informix' and the password is 'mypass', then to run the sample application (with it being named EzJava), you'd run it as follows:
NOTE: This is supposing that the appropriate jar file is included in the CLASSPATH environment variable. If not, you must include it with the "-cp" flag.
Keep in mind that the query executed by the sample app will probably not work with your db, so change the query accordingly to test it out with a table of your own. For this example, you need to have set up your logging mode to be buffered or to log. Otherwise you'll get an exception when trying to commit your connection. If you want to use the "stores_demo" database, to create it run the "dbaccessdemo -log"
4-Make your own app. If you were able to compile and run the sample app without a problem, congratulations, you are now ready to develop your own app. Enjoy!
Info center link to JCC: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.jccids.doc/jcc.htm
Establishing JCC connections with IDS: http://www.ibm.com/developerworks/blogs/page/idsteam?entry=establishing_jcc_connections_in_11
IBM Data Server Driver for JDBC and SQLJ Version 3.52 (This is platform independent) (JCC driver): https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd
IDS on Mac (free trial available here): http://www-01.ibm.com/software/data/informix/mac/informix/mac/
To All Informix users, I would like to encourage you to take a look at our new IBM Data Studio Developer 2.1 offering. There are significant enhancements we think that IDS developers will like.
We now have new tools to view update statistics and perform object management tasks easily, such as specifying data partitions (either round robin or expression-based) and raw tables. Also, trigger support has been greatly improved to take advantage of the ability in IDS to have before, after, and ‘for each’ triggers, whereas only ‘for each’ was supported previously. These capabilities are available at no charge. Guy Bowerman talks a bit more about these features in his blog.
In Data Studio Developer, which includes the base tooling and all the value-add capabilities, we also have many new features that IDS developers can take advantage of.
Using our previous releases, IDS developers could improve productivity by using the SQL content assist and validation capabilities in the Java editor and to generate applications quickly using the pureQuery API. You could capture SQL from any Java application and correlate which SQL was issued to the database and from where in the application. Using Data Studio, you could gain insight into which database tables and columns were used by the SQL. Developers and DBAs could easily isolate poorly performing queries in the application and work better together, to assess changes needed in the application as a result of database schema changes.
Data Studio Developer 2.1 has even more to offer for Informix developers and DBAs:.
You can now understand more about how the SQL in your application is performing using visual displays of elapsed time per SQL statement, and then you can change the SQL that is issued without having to change the application. Use these features from Data Studio and pureQuery Runtime together to help develop enterprise-ready applications before throwing it over the wall into production.
You can reduce or eliminate the risk of SQL injection because now it is possible to ensure that only SQL that was previously captured and approved is allowed to execute. This is available for dynamic SQL not just static, so by using with pureQuery Runtime, Informix applications can take advantage of this.
You can share database connections, reducing the amount of communication required to share details of connection information between administrators and all the developers who need to use the databases. Here's an entire article just on this topic.
See my article for more details on what's available for Informix developers and DBAs. We even have a set of videos that you can check out that follow along with the article.
The download for Data Studio Developer 2.1 should be available soon.
This single package contains both the no-charge, perpetual features available and supported for IDS customers with up-to-date maintenance contracts, but also 30-day trial features of the value-added capabilities of the priced Data Studio Developer product. The trial features are optional during installation, but I think you’ll be happy if you give them a test drive. In the meantime don’t hesitate to send me any feedback or suggestions that you may have. Then let me know either here or on the Data Studio Forum what you think of the new release. -- Sonali Surange
IDS Experts
