Other Issues

IIUG Developer's Desktop - The Newsletter For IDS Developers
Volume I, Issue 2

In This Issue

From the Editor - News and Notes
Configuring IDS to use DRDA
Using Data Studio to Expose your IDS queries as Web Services
Understanding Locks, Semaphores, Latches, Mutex and Conditions


From the Editor - News and Notes

Welcome to the second issue of the IIUG Developer's Desktop. I hope that you enjoyed the first Issue, and hope that you bear with us as we make changes along the way. As you can see we already have a bit of a different look.  Our first issue was mostly about JDBC/JCC and with this issue we will be talking about IBM's latest offering Data Studio.  With that said, it's time to talk about our current notes and News:

Those are the news and notes for now. I hope you enjoy this issue.

Mark Jamison
Editor IIUG Developer's Dekstop



Configuring IDS to use DRDA  By: Mark Jamison

While working with Data Studio, you will notice that in addition to that product you also get the Java Common Client (JCC) driver. In fact, the Data Studio uses the JCC driver by default. On the one hand, this is absolutely great, because it helps you see where IBM's long range direction is with clients. On the other hand, JCC requires some special setup on the IDS instance level. This article will detail how to setup you instance to handle JCC connections.

What happened to my SQLI?

The reason why JCC connections require engine configuration changes is the default means by which IDS instance communicate is through the SQLI protocol, whereas JCC needs the DRDA protocol. Currently IDS does not allow for both SQLI and DRDA to connect through the same listener thread, so this means is we need to configure a listener thread and port to accept DRDA calls.

Setting up DRDA Connections for Unix environments:
This is done in by making a simple change to the SQLHOSTS file as shown below:

<instance name> drsoctcp <hostname> <service/port>

or

<instance name> drsipcshm <hostname> <service/port>

As you can see the protocols look the same as our old ones, except instead of the prefix ol, you use dr.

And full example is below:

cheetah_dr drsoctcp gofish sqlexec2

After changing the SQLHOST all you need to do is place this entry in you onconfig file, in the DBSERVERALIASES variable. So if you use the example SQLHOSTS entry above and had no aliases currently you would have the following in you onconfig file:

DBSERVERALIASES cheetah_dr

Now just bring your instance off-line and then back on-line, and then we are ready to use JCC.

Setting up DRDA Connections for Windows environments:

To setup a DRDA port for windows you need to use setnet, specifically you need setnet version 3.00.TC2 or better. Once you execute setnet, go to the server information and set up a server as normal, just when you get to the Protocol Name, select drsocttcp as shown below:

Set Net picture

After selecting drscotcp, just hit OK. From there all you need to do is place this entry in you onconfig file, in the DBSERVERALIASES variable, and stop and restart the IDS services.


Using Data Studio to Expose your IDS queries as Web Services  By: Sathish Sadagopan

Data Studio provides a comprehensive suite of integrated tools for development database administrators and application developers. You can use the Informix JDBC driver or the IBM Data Server Driver for JDBC and SQLJ to connect to Informix databases. You can now use the workbench to create Web services that expose database operations (e.g. SQL SELECT and DML statements, XQuery expressions, or calls to stored procedures) to client applications. You can create Web services in a data development project, and you can easily add stored procedures and SQL statements by dragging and dropping them to a Web service or by using a wizard. You can also use the workbench to deploy Web services to or undeploy them from a Web server. IBM Data Studio can create Web Services from SQL Select statements and deploy it to asupported web server. Applications can access the Web Services using SOAP/HTTP, REST-GET or REST-POST messaging protocols.

This article will show the steps needed to expose IDS SQL statements as Web Services.

1. Setup connectivity to a database
2. Create a data development project
3. Create SQL Script with a host variable.
4. Configure connection to WAS
5. Create a web service and add a database operation to the web service
6. Deploy the web service to an application server
7. Test the Web Service
8. Software downloads

1. Setup connectivity to a database:

In the database explorer section right click “Connections” and click new.

 New Connection

In the JDBC driver drop down selection choose “Informix JDBC Driver”. You can use the IBM Data Server Driver to communicate with the Informix V11 database. The IBM Data Server Driver is a common API that uses DRDA to connect to all IBM Data Servers.

Enter the Database name, host, port and INFORMIXSERVER values.
Enter the User ID and Password and click on “Test Connection” to ensure that the connection is valid.

In this example the ‘stores’ database is used for demonstration purposes.

Refer to this link for information about configuring DRDA for IDS V11.
http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.admin.doc/admin155.htm?resultof="drsoctcp"

2. Create a data development project:

Once you have IBM Data Studio installed, launch it and create a new data development

project. Click File > New > Data Development Project

 New Development Project

We can use the connection created in the previous step.

Select Connection 

 

Click on Finish.

Your Data Project Explorer window will now look similar to this:

 Data Project Explore

 

Your Database Explorer window will look similar to this:
 

Database explorer

 

3. Create SQL Script with a host variable:

In the ‘Data Project Explorer’ right click on SQL Scripts -> New ->SQL or XQuery Script.

 New SQL

 
Provide a Name for the SQL statement and ‘Finish’.
In the body of the SQL statement type in this SQL statement: 

Select * from customer where state = :state

Ctrl – S will save the script.

Right click on the SQL statement and click “Run as SQL”. This causes the SQL

statement to be executed against the database. You will see a window pop up prompting for parameter values. This is because we declared a host variable :state.

 Host variable

Enter a value for the host variable, a value of NJ has been entered in the above example.
The statement will be run when you click ‘Finish’. The results will look similar to the

screen shown below:

SQL Output 



4. Configure connection to WAS CE

This article assumes that you have already downloaded and installed Websphere App Server community Edition. Refer to the ‘Software downloads’ section of this article to download and install Websphere Application Server – community edition.

Click Window->Show View->Other-> Servers as shown below.

Show View

Show View part 2

In the Servers view, right click and create a new server as shown below

View Server

If you have not previously installed WAS you will get the following screen:

New WAS Install

Enter the installation directory of WAS CE, it is D:\WASCE in the above example.
Click next.

If WAS CE has been installed, or you have completed the previous steps you should see a window like the following:

New WAS SERVER

Choose WAS CE V1.1 Server, if that is the server you have installed.

Click Next which will bring up a window like the one below:

New WAS Server part 2.


Unless you have specific changes you wish to make to the above Click finish.

Right click and start the server if it not already started as shown Below:

View Server

Congratulations, you now have a WAS server set up to take Web Service functions.

5. Create a web service and add a database operation to the web service

So now that we have a connection to the database and a connection to a WAS server, it's time to create the web service, and add database operations to it.

First Right click on Web Services in the ‘data project explorer’ and choose new. The next screen will look similar to the screen below.

New Service


Enter a name for your web service, it is ‘statecustService’ in this case.

Next drag the “run_state_cust.sql” from the SQL Scripts tree and drop it into ‘statecustService’ under Web Services.  And now  you have a  web service ready to go.

6. Deploy the web service to an application server

Right click on the ‘statecustService’ and choose build and deploy. Your screen should look similar to the one shown below.

Deploy Service

 Deply Web Service

Ensure that the ‘Register database connection with Web Server’ option is checked.

You should get a message that states that deployment was successful.


7. Test the Web Service

Once the deployment is successfully completed, right click on the ‘statecustService’ web service and Launch the web service explorer.

Test Web Service part 1

At this point you should be able to see the web service explorer and the three bindings which have been built. You should see a screen like the following:

Test We Service Part 2

At this point you will now be able to invoke the actual web service, in the screen below the service was invoked using SOAP.

Invoking Web Service

You will also be able to invoke the web service from a web browser using a URL. The URL in the case of our example will be:

http://localhost:8080/CheetahProjectstatecustService/rest/statecustService/run_state_cust?state=CA

You can change the value of the state column in the URL to see the results change for the service. So you can see  Data Studio makes building web services a very simple and easy process.

8. Software Downloads:

A Trial version of IDS can be downloaded from http://www14.software.ibm.com/webapp/download/search.jsp?go=y&S_TACT=&S_CMP=&s=&q=informix&S_CMP=rnav

Websphere Application Server community edition can be downloaded http://www.ibm.com/developerworks/downloads/ws/wasce/

IBM Data Studio can be downloaded https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-ids



Understanding Locks, Semaphores, Latches, Mutex and Conditions By: David Fraser

Locks, semaphores, latches, mutexes, and conditions, these are five terms that are often misunderstood and confused.  This is partly because they lack documentation, but the terms are often interchanged both inside and outside of Informix.  They also can mean different things within the Informix product than they might have meant in other classes that you have attended.  This adds just another level of confusion.

The intention is to first define the basics of each of these terms in relation to Informix and then see how each of them is currently used within IDS.  Some discussion will also be directed toward how these were used in past versions as a way to aid understanding.

Locks

Within the context of Informix a lock is used to reserve access to a database object.  This can be a database, table, page, row, key, or a number of bytes on a page.  These locks are tracked in a table in shared memory.  Access to this table is controlled by mutexes  It is possible for multiple sessions to have a lock on the same database resource.  These locks can be seen by using the onstat -k command.  More about locking is covered in the next chapter.

Semaphores

A semaphore is an operating system resource. Semaphores are created in sets, with the creating program specifying how many semaphores will be in the set.  This is done by an oninit process using the semget( ) call.  The creating program passes two arguments, a key to be associated with the semaphore group and the requested number of semaphores.  Once the semaphores are created it is up to the creating program to manage them. There are operating system limitations, which are tunable, on the number of semaphores in a set and the total number of semaphores that can exist on the system at any given time.  Operations are permitted on the semaphores using the semctl( ) and semop( ) commands.

One of the more common way to use a semaphore is to put a process to sleep on the semaphore waiting for a particular event to wake it up.  Informix uses semaphores in this way for a VP process that does not have any further work to do.

The UNIX ipcs command shows information about the semaphores in use on the system.  There is no direct way to tell which ones are associated with a particular IDS instance.  Here is an example of the output from ipcs:

% ipcs -as

IPC status from <running system> as of Wed Feb 30 12:49:12 2008
T  ID KEY       MODE         OWNER  GROUP    CREATOR CGROUP   NSEMS

Semaphores:

s  0  00000000  --ra-ra----  root   informix root    informix 8

s  1  00000000  --ra-ra-ra-  root   informix root    informix 25

s  2  00000000  --ra-ra-ra-  root   informix root    informix 25

s  3  00000000  --ra-ra-ra-  root   informix root    informix 2

Latches

A latch was the first method that was used in Informix products to protect shared memory resources from being accessed by multiple users at one time. Each process accessing shared memory has to know and follow this  same protocol to prevent corruption.  The latch structure looks like this:

typedef struct
    {

    VOLATILE mt_primitive_lock_t
plock;  /* primitive lock    */
    unsigned long nwait;   /* # of times had to wait  */

    unsigned long nloops;  /* # of spin loops in wait */

#ifdef SPINCHECK
    void     *holder;      /* holder of the spin lock        */
#endif
    } LOCK_T;

Notice that the name of this latch structure is LOCK_T.  This presents just one of the many potential points of confusion.

This method to protect shared memory was used exclusively in pre-IDS engines. A single process obtained a plock, through an assembly routine on most machines, then the user could change the resource that this latch was protecting.  Plock is defined as a char(1) on most ports.

If plock was set to one then the latch was in use and the user incremented the nwait flag and set the waiting field in their user structure to the address of the resource they were waiting for.  Once the user released the latch, it was their responsibility to go through the wait list in the user table and notify all of the users that were waiting for this latch.  There was no order in which processes got the released latch.  One user obtained the latch and the others reset themselves to wait.

When a process is waiting, it has to do something.  In most pre-IDS versions the engine process immediately put itself to sleep on a semaphore.  It  was the responsibility of the process releasing the latch to wake it.  In later versions, and as multiprocessor machines became more mainstream, the process would spin in hopes of getting the latch next time it checked.  This spinning is a tight loop in the code that does nothing.  Every so many loops it would try for the latch again.  This is more CPU intensive, but requires less operating system overhead than swapping the process in and out as it goes to sleep on the semaphore.

Latches are still used just as it was in some places, but it has also been enhanced and used as part of a new mechanism.

Mutexes

The mutex can be thought of as a upscaled latch.  The primary disadvantage of a latch was that it contained no method of queuing waiting threads.  The mutex structure does this.  It is really a latch structure with added information.  Notice that the older LOCK_T structure is present in the new MT_MUTEX structure.

struct _MT_MUTEX
{

    LOCK_T   lock;   /* mutex lock        */

    short    flags;  /* see MTSET_MUTEX defines */

    short    lkcount;      /* lock count by same thread      */

    TCB     *waiting;      /* queue of waiting threads       */

    TCB     *holder; /* owner of mutex lock     */

    LINK(MT_MUTEX) mutex_link;    /* link for mutex list     */

    mint     mutex_id;     /* unique identifier       */

    char     mu_name[MT_NAME_SIZE+1]; /* user supplied name   */

              /*  - may be null   */

    short    type_id;      /* type identifier         */

    RSTAT_T *rs;     /* statistics structure    */

};

The mutexes for the IDS instance are a linked list, stored in the element mutex_link.  The primary element is lock, the old latch type.  This is the latch that the thread must acquire to check the owner column and see if it is in use.  It has also been enhanced by a queue of waiters (*waiting) which has its own latch (waitlock) to protect access to it.  In addition a number of other columns have been added and some statistics.

The threads are awakened and given the resource in the order that they have requested it.  It is the responsibility of the thread releasing the resource to awake the first thread on the queue, change the head of the queue, and put the thread on the ready queue.

The spinning mechanisms described under latches is still used, but only to get one of the latches in the mutex.  The threads are never put to sleep on a semaphore to wait since there are now appropriate VP queues for them to wait in.

The statistics in the MT_MUTEX structure is shown below for reference.

typedef struct _rstat_t
    {

    unsigned long    nwaits;              /* number of waits         */

    unsigned long    nservs;              /* number of services      */

    unsigned long    current_qlen;        /* current queue length    */

    unsigned long    total_qlen;          /* total queue length      */

    unsigned long    max_qlen;            /* maximum queue length    */

    double           wait_time;           /* cumulative wait time    */

    double           serv_time;    /* cumulative service time */

    unsigned long    max_wait_time;       /* maximum wait time       */

 
}  RSTAT_T;

Conditions

A condition is actually a special type of mutex.  While mutexes are used to manage synchronous access to a resource, a condition defines a particular event that must occur before waiting threads can continue.  A checkpoint is a good example of a condition.  During a checkpoint, all user threads are queued by a condition wait structure until the completion of the checkpoint.  Once the conditional test becomes true, in this case, the completion of the checkpoint, those threads waiting for the condition can proceed.

Here is the structure of a condition:

struct _MT_CONDITION
{

    LOCK_T  lock;

    TCB    *waiting;

    mint    type_id; /* type identifier         */

    mint    cond_id; /* unique identifier       */

    LINK(MT_CONDITION) cond_link;

       /* link for condition list */

    char    co_name[MT_NAME_SIZE+1];

       /* user supplied name - may be null      */

    RSTAT_T *rs;     /* statistics structure    */

};

Like mutexes, the condition structure also contains a latch structure and a wait list.

Monitoring Mutexes

There are three onstats that display mutexes that are in use.  The -s options only reports on a subset of the available mutexes.  The -g lmx and -g wmx options show all mutexes in the instance.  The first list mutexes that have an owner and the second lists mutexes with waiters.

There are seldom entries on these onstat  outputs.  This is simply because mutexes are meant to be held for a very short time.  Finding them on a onstat output would be unlikely.  These are the ones that are checked for the onstat -s output:

Mutex  name  What they protect
userthreadsuserthread table
transtransaction table
lockfrremoving or adding a lock
to the lock free list
lockdlused for sunchronization of
deadlock detection
ckptcheckpoint information
archivearchive information
dbspacedbspace table
chunkchunk table
logloglogical log
physlogphysical log
physb1physical log buffer 1
physb2physical log buffer 2
flushctlPage cleaner table
altlatchCounter of alter tables.
timestampTime stamp changes.
traceTraces, if turned on.
fllushr%dpage cleaner
LRU%dLRU queue
lh[%d]Lock hash bucket
txlk[%d]Transaction table entries
bh[%d]Buffer hash buckets
bf[%d]Buffers
bbf[%d]Big buffer latches
%d is the element number in the table, and the information which
comes after "trace", is for each element in the table.

Each time onstat -s is executed these mutexes are checked for either an owner or a waiter.  If either exists then they are shown.

Here is an example of the comparison of an onstat -s and onstat -g lmx at the same point in time.  Notice how the lists differ, but really the same mutexes are held internally.

onstat -s

Informix Dynamic Server 2000 Version 9.40.UC2 -- On-Line -- Up 01:35:22 -- 8976 Kbytes

Latches with lock or userthread set
name   address       lock   wait   userthread

bh[5]  a050f0c       1      0      a25f180

onstat -g lmx

Informix Dynamic Server 2000 Version 9.40.UC2 -- On-Line -- Up 01:35:22 -- 8976 Kbytes

Locked mutexes:
mid    addr   name   holder lkcnt  waiter waittime

2068   a050f0c       hash   33     0

2812   a14b970       ddh chain     33     1

It is also interesting to note that the same mutex, address a050f0c, has a different name in both lists.  That is simply a differrence in the naming conventions of the two routines that print them out.

Monitoring Conditions

To display a list of conditions with waiters, use the onstat -g con command.  You are more likely to see certain conditions displayed than you are mutexes because the duration of a condition is an undetermined length of time.  The time required in the wait queue is not based on synchronous access to a resource, but is based on an event that must occur that causes the condition to be met and, therefore, allows waiters to continue. 

You can also see threads and sessions that are waiting on conditions by using other onstat commands, shown above.  Later, you will see examples of how to use these commands to trace a condition to a thread, session, and process

Here is an simple example of the onstat output listing conditions on an inactive IDS instance:

% onstat -g con

Informix Dynamic Server 2000 Version 9.40.UC2 -- On-Line -- Up 01:35:22 -- 8976 Kbytes

Conditions with waiters:

cid      addr     name          waiter   waittime

1650     a4df6b0  sm_read       224      76

Monitoring Semaphores

In IDS there are three ways that semaphores are used in DSA.

The first is for synchronization between users connecting through a shared memory connection.  The semaphores are used to signal if a message has been left in the communication segment by either the server or the client for the other.  If the semaphore is set then a message is waiting.

Semaphores for this use are allocated using this formula:

#_shared memory_users_per_poll_thread + 2

This number is allocated for each shared memory poll thread that is configured.  The semaphores for each poll thread are allocated in a separate group.

The second use for semaphores is for putting VPs to sleep when they do not have any work to do. A thread in the ready queue for the VP triggers the VP to wake up.

The equation for allocating these semaphores is:

total = ADM + MSC + CPUVPS + AIOVPS + PIO + LIO + 2_if_MIRROR + ADT + OPT + total_NET

ADT+OPT are optional. Two additional are allocated if the MIRROR flag in onconfig is set.  These are for possible use with the additional physical and logical log VPs

The semaphores for the VPs are allocated in one set, assuming the operating system permits it.

If a VP is added or dropped a semaphore set with a single semaphore will be allocated or deallocated.

This semaphore usage is shown  in onstat -g sch:

% onstat -g sch

Informix Dynamic Server 2000 Version 9.40.UC2 -- On-Line -- Up 01:35:22 -- 8976 Kbytes

VP Scheduler Statistics:

       vp     pid    class  semops busy waits    spins/wait

       1      17433  cpu    3936   0      0

   2   17434  adm    0      0      0

   3   17435  lio    9      0      0

   4   17436  pio    9      0      0

   5   17437  aio    31     0      0

   6   17438  msc    2      0      0

   7   17439  aio    15     0      0

   8   17440  str    2      0      0

The final way that semaphores can be allocated is if the relay module is being used for communication to an older engine.  In this case, two semaphores per relay module are allocated.