In IDS, when a new row containing a column that can vary in length is to be inserted on a page, the row will be inserted only if the page contains sufficient space to allow the new row to grow to its maximum length. This can result in low disk space usage. In IDS 11, you can change this default behavior by setting the configurationparameter MAX_FILL_DATA_PAGES to 1. When MAX_FILL_DATA_PAGES is enabled, the server will add a new row to a page ifafter adding the row, at least 10 percent of the page is free for the future expansion of the rows. The database server needs to be restarted after changing this configuration parameter.
To take advantage of this setting:
Advantages of enabling MAX_FILL_DATA_PAGES are:
The possible disadvantages of enabling MAX_FILL_DATA_PAGES are:
Example: Suppose you have a table with a column of type lvarchar(6000). If the dbspace in which the table resides is of 4K or 8k pagesize, you might see a lot of unused space in the pages. If you do not have to make updates which would cause most of the rows to expand to their full length, you might want to enable MAX_FILL_DATA_PAGES tobetter use the space in each page.
Suma Vinod[Read More]
Here we will look at each of the XML functions given in Part 1 with examples.
You can use these functions to create an XML row element for each row of a SQL query result. Each column in the rowis an attribute of the row element. genxml() is used for returned row values that are LVARCHAR(32739) or less. Forlarger values, you should use genxmlclob(), which returns a CLOB.
These functions process the rows without any specific order. If the order of the rows is important, you can use thederived table queries to get the result set in the correct order, and then apply the functions on the result set.
select genxml(row (customer_num, fname), "row") from customer;Output from the above sql:
<row customer_num="101" fname="Ludwig "/><row customer_num="102" fname="Carole "/><row customer_num="103" fname="Philip "/><row customer_num="104" fname="Anthony "/>To order the results on fname, use the following sql:
select genxml(row(num,name),"row") from (select customer_num, fname from customer order by fname) as vt(num, name);To select all columns in the table, use the following sql:
select genxml(customer, "row") from customer;genxmlelem()/genxmlelemclob()
These functions return each column value as separate elements, in contrast to genxml(), which returns column valuesas attributes of the row element.
select genxmlelem(row(customer_num,fname), "cust") from customer;Output from the above sql:
<cust><row><customer_num>101</customer_num><fname>Ludwig </fname></row><row><customer_num>102</customer_num><fname>Carole </fname></row><row><customer_num>103</customer_num><fname>Philip </fname></row><row><customer_num>104</customer_num><fname>Anthony </fname></row></cust>You can select all columns in a table by passing the table name as the first argument as shown below:select genxmlelemclob(customer, "cust") from customer;genxmlschema() & genxmlschemaclob()
select genxmlschema(customer, "cust") from customer;Output from the above sql:
<?xml version="1.0" encoding="en_US.819" ?> xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.ibm.com" xmlns="http://www.ibm.com" ElementFormDefault="qualified"> <xs:element name="cust"> <xs:complexType> <xs:sequence> <xs:element name="customer_num" type="xs:serial"/> <xs:element name="fname" type="xs:char(15)"/> <xs:element name="lname" type="xs:char(15)"/> <xs:element name="company" type="xs:char(20)"/> ...genxmlquery() & genxmlqueryclob()
They are versatile functions that take a SQL query as argument and return the result set in XML.
execute function genxmlquery('cust','SELECT customer_num FROM customer');Output from the above sql:
<cust><row><customer_num>101</customer_num></row><row><customer_num>102</customer_num></row><row><customer_num>103</customer_num></row><row><customer_num>104</customer_num></row></cust>genxmlqueryhdr() & genxmlqueryhdrclob()
These functions return the same data as genxmlquery() but with an XML header.
execute function genxmlqueryhdr('cust','SELECT customer_num FROM customer');Output from the above sql:
<?xml version="1.0" encoding="en_US.819" ?><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"><cust><row><customer_num>101</customer_num></row><row><customer_num>102</customer_num></row><row><customer_num>103</customer_num></row><row><customer_num>104</customer_num></row>extract() and extractxmlclob()
These functions evaluate an XPATH expression on a XML column, document, or string. These functions are identicalexcept that extractxmlclob() returns a CLOB instead of LVARCHAR. They are used to return an XML fragment of theevaluated XML column, document, or string. They are compatible with the Oracle extract()function.
execute function extract('<name><first>fred</first></name>','/name/first');Output from the above sql:
(expression) <first>fred</first>extractvalue() and extractxmlclobvalue()
These functions return the value of the XML node in contrast to extract(), which returns the XML node. They are compatible with the Oracle extractvalue() function.
execute function extractvalue('<name><first>fred</first></name>','/name/first');Output from the above sql is just the name without the XML tags:
This function returns 1 if the specified XML node exists in an XML document. It is compatible with Oracle exists() function
execute functionexistsnode('<name><first>fred</first></name>','/name/first')Ouput from the above sql:
Parse an XML document or fragment to determine whether it is well formed. This function returns an XML document orfragment if the input XML is well formed else an error is returned.
SELECT idsxmlparse('<purchaseOrder poNo="124356"><customerName>ABC Enterprises</customerName><itemNo>F123456</itemNo></purchaseOrder>') AS PO FROM systables where tabid = 1;Output from the above sql:
<purchaseOrder poNo="124356"><customerName>ABC Enterprises<itemNo>F123456</purchaseOrder>Note that the string in the quotes should not have any carriage returns.[Read More]
IDS 11 provides support for XML publishing thus enabling applications to publish SQL results as XML, query XML dataand return XML. It also provides the framework to map an XML schema into a relational schema and to shred an XML document into relational tables.
The XML functions available in IDS 11 are summarized in the following table:
To use the XML functions in IDS 11:
I will talk about each of the above functions with examples in Part 2.
Suma Vinod[Read More]
In previous IDS versions, the logging mode of the temp table matches the logging mode of the database in which theyare created. In other words, temp tables created in a logged database will be logging by default and temp tables created in a non-logged database will be non-logging by default. If you do not want your temp tables to be loggingin your logged database you have to use the "with no log" clause when you create it.
For example the following SQL creates the temp table temptab1 with no logging.
create temp table temptab1 with no log;
In IDS 11, you can control the default logging mode of temporary tables. If you set the configuration parameter TEMPTAB_NOLOG to 1, temp tables created in a logged database will be non-logging by default. This is useful inreplication environments where you create your databases logged but do not want to replicate your temp tables.
If TEMPTAB_NOLOG is set to 0, this feature is disabled.
OpenAdmin Tool for IDS, the PHP-based administration console for IDS, is now available for downloadfrom the Open Source download site.
High-level Summary of Install steps
If not already installed, install the Informix Client SDK and set INFORMIXDIR in your environment to be the location where CSDK is installed. (Note: CSDK 3.00.UC1 or later is recommended.)
To install Informix CSDK, extract the product files and run as root installclientsdk. Follow the instructions on screen to install CSDK.
Important: Even if you are installing on a Linux-x86_64 machine, you will need a Linux 32-bit version of Informix CSDK. This is because Linux XAMPP is a 32-bit build; so to build a compatible PDO_INFORMIX module, you will need the Linux 32-bit version of Informix CSDK.2. Download XAMPP and extract files.
Important: You must download both the Linux runtime and development packages; the development package is necessary in order to be able to compile the PDO_INFORMIX driver.
3. Download and extract PDO_INFORMIX
4. Configure PDO_INFORMIX
Note: For more information about building the PDO_INFORMIX module, go to the DeveloperWorks article on that topic.
5. Update the php configuration file (php.ini)
Navigate to the /opt/lampp/etc directory, and open the php.ini file in a text editor.
Edit php.ini to enable the Informix PDO driver:
6. Download and Extract the OpenAdmin tar file in the htdocs directory.
7. Start the Apache web server.
Using a web browser, go to http://< machine_name >/openadmin/install where < machine_name > is the name of the machine where you have installed the products. Follow the installation instructions to install OpenAdmin Tool. After the first install screen a check will be made to ensure you have the correct PDO drivers:
Enter the base URL for your machine, language defaults, and optionallythe Google maps key for your machine URL:
8. Start using OpenAdmin
Once installation is complete you can go to the main page (http://< machine_name >/openadmin in this example), click Admin and start adding IDS 11.10 connections. Important: If you are using a version of CSDK prior to 3.00.UC1, there must be aSQLHOSTS entry on the web server machine for each connection you add .
Once a connection is defined you can return to the main screen and connect:
The connection administration allows you to create groups of connections, and assign a password for each group, making it easier to administer a large number of instances.
Erika Von Bargen[Read More]
Roles Folder:The Roles Folder, as it's name implies, gives you a view of all theroles created for the table, and a visual means to create new roles oralter existing roles. To get a quick look at how this folder works,let's create a test role that will have complete access to the customertable.
As you can see you can do the following:
Mark Jamison[Read More]
Note if you have missed any articles in this series, check Introductionand Table of Contents
TheDatabase Explorer WindowNow that you have a workspace, and a connection established, it is timeto take a quick look around the options available to you in theDatabase Explorer window. By default this window is located in thelower left hand corner, and on start up looks like this:
As you can see this has a tool bar as well as the Connections folder.Let's take a quick look at the options you have on the tool bar.Traveling from Left to right, you have:
Now let's open up our connections folder in this window. We willbe looking at the stores_demo database. So first we open theConnections folder, and then the stores_demo connection folder, andfinally the stores_demo database folder. And when the folders are open you will see something like the following:
As you can see, each connection tells you what type of instance you areconnecting too, in my case all of my instances are 11.10, thoughDeveloper Workbench reports them as 11.0. You can see thatthe Database folderhas three sub-folders. Those Folders are:
EstablishingJCC connections for IDS
While working with the open beta of IBMData Server Developer Workbench 9.5 , I noticed that inaddition to that product you also become open beta participants for theJava Common Client (JCC) driver. In fact, the Workbench uses the JCCdriver by default. On the one hand, this is absolutely great, because ithelps you see where IBM's long range direction is with clients. On theother hand, JCC requires some special setup on the IDS instance level.This article will detail how to setup you instance to handle JCCconnections, and then will use the Developer Workbench to show how youconnect using JCC.
Whathappened to my SQLI?The reason why JCC connections require engine configuration changes isthe default means by which IDS instance communicate is through the SQLIprotocol, whereas JCC needs the DRDA protocol. Currently IDS does notallow for both SQLI and DRDA to connect through the same listenerthread, so this means is we need to configure a listener thread andport to accept DRDA calls.
This is done by making a simple change to the SQLHOSTS file as shownbelow:
<instance name> drsoctcp<hostname> <service/port>
<instance name> drsipcshm<hostname> <service/port>
As you can see the protocols look the same as our old ones, exceptinstead of the prefix ol,you use dr.
And full example is below:
cheetah_dr drsoctcp gofishsqlexec2
After changing the SQLHOST all you need to do is place this entry in youonconfig file, in the DBSERVERALIASES variable. So if you use theexample SQLHOSTS entry above and had no aliases currently you wouldhave the following in you onconfig file:
Now just bring your instance off-line and then back on-line, and then weare ready to use the Developer Workbench.
Setting up yourJCC Driver for 11.10 in Developer Workbench 9.5Now that you have set your IDS instance up to accept JCC driverconnections, let's establish one using the Developer Workbench 9.5
In your Database Explorer window, select Connection->New Connectionas below:
This will bring you to the Connection Settings window. Below is anexample of the JCC driver with all field filled in:
As you can see, you do not need an INFORMIXSERVER for your connection,just the Database, Host and Port Number. Once you configure this foryour server, just click the TestConnection button. After that just click the Finish button. Younow have a JCC connection that you can use for your Applicationrunning against IDS 11.10
Mark Jamison[Read More]
Note if you have missed any articles in this series, check Introduction and Table of Contents
Setting up yourworkspace and connecting to IDSNow that you have installed the Developer Workbench it is now time torun it. ON the first run, you should be asked to set up yourworkspace. This should give you a screen like the following:
Once you select this, the Workbench takes a little while to build yourworkspace. Once done however you get the classic EclipseWelcome screen. I have highlighted your workspace in the followingscreen shot , because that is the next place you want to go:
Once you click on the workbench you get the generic workbench. The nextstep at that point is to start a project , and establish a connection:
Setting up a new project to handle queries is fairly straightforward,and can be reached very quickly , all you have to do is select the DataDevelopment Project option as in the following screen shot:
Selecting the above puts you to the new Data Development projectscreen, as shown below:
As you can see, there are some DB2 flavored words here.Schema in DB2, is the IDS equivalent of a database, and as such, keepthe default radio button, as seen in the screen shot above, set. I would recommend that you giver your project a meaningfulname, but that is completely under you control. In my case I listed itas "Mark's Test project for Blog".
This brings us to the next page which assigns an existing databaseconnection, or creates a new one. Since this is our first time through,just hit the next button, as we will need to create a new connection.
The Connection parameters gives you several options for connections, aswell as several data server choices. Take a look at the below screenshot:
This is a standard Informix JDBC connection string at this point. Thescreen shot above differs slightly from what you see as the defaultsettings, as I have selected the "Informix JDBC driver" instead of the"IBM Data Server Driver for JDBC and SQLJ." In short I took the JDBCdriver instead of the JCC driver. The main reason is because JCC itselfis in beta, and I wanted to use something I knew how to connect withand trace. JCC also requires a DRDA listener thread, and I don't have one set up for the instance I am using. Update : Check out Establishing JCC connections in 11.10 if you want to use JCC connections.
Fill the remaining fields above with the normal Informix connectionsettings, and test you connection. Once you connection is working,click on the next button.
This final screen allows you to filter your schema. Here is the screenshot :
Please note: The default is only for users who have IDS on a windowsplatform. Since schema id is actually a user id in IDS, filtering onINFORMIX means that no information of any kind will return for anydatabase objects if your IDS instance is on a Unix platform. The reasonof course is the user id is case sensitive, and informix != INFORMIX.Quite honestly I suggest you just disable filtering for now, when Icover the the Database explorer I will show you how to change thefilter how you want to.
As soon as you have selected your filter objects, you will finally seethe workbench laid out, and ready to be used for a Data Developmentproject. Here is mine :
In the next article I will discuss the Database Explorer Window in thelower left hand corner.
Mark Jamison[Read More]
CommonDevelopment ToolOne of the big ideas recently proposed by IBM, and further proof of our commitment to IDS, has been the idea of a common setof clients tools (like a Java Common Client (JCC), for example). If you aregoing to have a common set of clients, or programming interfaces, itmight not be a bad idea to have a common development tool that willallow you to use these common clients, for both DB2 and IDS. Well IBMhas released in beta an Eclipse based common tool that allows that. Forthose who want to jump right in here is the link:
IBMData Server Developer Workbench 9.5
As with many things IBM , since this is an open beta the actual name atthe time of release may change. I will be blogging on thisproduct over the next few weeks covering its capabilities from the IDSside of the tool. So far there will be 5 parts to this report, notcounting this introduction.
Part 1 - Installation for IDS
Part 2 - Setting up your workspace and connecting to IDS.
Part 3 - The Database Explorer window.
Part 4 - ER Diagramming
Part 5 - The SQL Builder and The SQL Editor Windows.
Part 6 - PureQuery and IDS