IDS Experts




For a complete list of current SQL reserved words, see the IBM Informix Guide to SQL: Syntax.


Sanjit Chakraborty[Read More]

New Shared Memory Segment

cheetahblog 1,296 Views

A new shared memory segment has been introduced in the Informix version 12.10. The 12.10.xC2 onwards the bufferpool has been moved out of the resident segment and new bufferpool segment has been added  which shown as class 'B' in the 'onstat -g seg' output. The BUFFERPOOL configuration parameter determines the amount of buffers allocated to this new segment when the database server is started.

 

IBM Informix Dynamic Server Version 12.10.UC2DE -- On-Line -- Up 00:20:35 -- 85899522520 Kbytes

Segment Summary:
id         key        addr     size       ovhd     class blkused  blkfree
32768      52564801   44000000 3424256    243124   R     836      0       
65537      52564802   44344000 67108864   394328   V     6776     9608    
98306      52564803   48344000 110305280  1        B     26930    0       
Total:     -          -        180838400  -        -     34542    9608    



-Rashmi Chawak
 

Modified on by cheetahblog

New Storage Manager

cheetahblog 1,849 Views

Informix introduced the PSM (Primary Storage Manger); a new backup storage manager in version 12.10 that replaced the previous ISM (Informix Storage Manager). The PSM manages storage devices used for backup and restore requests that are issued by Onbar. This storage manager supports both serial and parallel processing for backup and restore requests. PSM is easier to set up and use, even in embedded environments. You just need to perform four simple steps to configure PSM and take an Onbar backup:

 

      1) Decide where you will create the PSM catalog. The default location is $INFORMIXDIR/etc/psm.

       2) Add devices for DBSPOOL and LOGPOOL

            - onpsm -D add   /backups/psm_spaces -g DBSPOOL -p HIGH -t FILE

            - onpsm -D add   /backups/psm_logs -g LOGPOOL -p HIGH -t FILE

       3) Onconfig settings - you can keep the default values

            - Set BAR_BSALIB_PATH to full path to libbsapsm.so

            - Set PSM_CATALOG_PATH to full path to PSM catalog directory

            - Set PSM_DBS_POOL   DBSPOOL

            - Set PSM_LOG_POOL   LOGPOOL

        4) Take an onbar backup

 

You use the Informix onpsm utility to manage storage for Onbar backup and restore operations, including parallel backups, that use file devices (disks). The onsmsync utility provides new commands that you can use to export backups to, and import them from, Informix PSM external device pools.

 

-Sanjit Chakraborty

Modified on by cheetahblog

Non-blocking Checkpoints - Part 1

cheetahblog Tags:  non-blocking interval checkpoint 1,579 Views
Prior to IDS Version 9, IDS supported a blocking checkpoint algorithmthat causes the system to block all transactional updates while thebufferpool is flushed to disk. In IDS Version 9 onwards, an additionalcheckpoint algorithm, called Fuzzy Checkpoint, was introduced. FuzzyCheckpoints limit the number of updates required to be flushed to diskduring checkpoint processing.

With the new non-blocking checkpoint algorithm in Cheetah, IDS ensuresthat we virtually don't block transactional updates while the entirebufferpool is flushed to disk. Transactionalupdates are blocked only for very small duration of time required to flush thepartition partitions (tablespace tablespaces) to the bufferpool and tograb the restart point. (Restart point is wherefastrecovery starts. We log the checkpoint and use that LSN as the restartpoint for the checkpoint.)

As part of this feature, the new "onstat-g ckp" command helps DBAs to view and analyze checkpointinformation and performance advisories. The "onstat -g ckp" command isexplained in more detail in thispost.

ScottLashley's whitepaper on developerWorks explains the internalsabout non-blocking checkpoints in detail.

In Part 2, we shall learn how to tune checkpoints and modify the relevant onconfig parameters (RTO_SERVER_RESTART, AUTO_CKPTS,AUTO_LRU_TUNING, etc.)

Mirav Kapadia[Read More]

OAT Group Summary Dashboard

cheetahblog Tags:  openadmin dashboard oat informix 7,798 Views

OAT Group Summary Dashboard

Monitor multiple Informix database server instances from a single dashboard

OpenAdmin Tool version 3.11 has a new Health Center > Dashboard > Group Summary page that allows you to monitor all of the database servers in your OAT group simultaneously from a single page.

This page saves you time if you have multiple Informix instances. No longer do you have to log in to each server individually. No longer do you have to click through multiple pages in OAT to check on everything from alerts, space, memory, sessions, and backups. Now all of this information is presented in a user-friendly way from a single screen.

image

For each server in your OAT group, you are presented with high-level summary information about the server status and activity. This information includes:

  • Server status (online or offline)
  • Number of alerts
  • Number of recent errors in the online activity log
  • CPU usage
  • Memory status
  • Space usage
  • I/O activity
  • Backup status
  • Number of current sessions

If you click on any of the cells on the Group Summary page, you will get a pop-up with more details as well as a drill-down link to where you can go in OAT to get further information or act on any issues identified. The following screenshots provide some examples of the pop-ups.

image

image

imageimage   image

 Status Thresholds

The Memory, Space, I/O, and Backups columns use red, yellow, or green icons to indicate status. These are determined by configurable thresholds.

The status thresholds for memory, space, and the chunk percentage of total I/O are configurable in the IBM® Informix Health Advisor Plug-in for OAT. To change the dashboard status thresholds, you change the thresholds for the associated alarm in the Default profile on the Health Center > Health Advisor > Alarms page. Use these steps to change a threshold in the Health Advisor:

  1. Use the following table to find the associated Health Advisor alarm name
  2. Dashboard status column Health Advisor plug-in alarm name
    Memory OS Free Memory
    Space Dbspace Free Space
    I/O: Chunk percentage of total I/O Chunk I/O Operations
  3. Go to the Heath Center > Health Advisor page in OAT.
  4. On the Profile tab, ensure that the Default profile is loaded.
  5. Go the Alarms tab, find the associated alarm name, and click the Modify Thresholds link.
  6. Edit the thresholds and click Save.

The status thresholds for backups are determined by the threshold in the sysadmin:ph_threshold table that indicates the maximum number of days between level-0 backups (REQUIRED LEVEL 0 BACKUP). To configure this value, go to the Task Scheduler > Scheduler page and click on the task named "check_backup". On the Task Details page, click the Edit Parameter button, edit the value, and click Save.

Data Refresh Rate

The server status information on the Group Summary page is refreshed based on the Refresh rate that is set by the slider at the top of the Dashboard page. However, to improve performance, the page is refreshed with data that is cached locally in the OAT connections database. The refresh interval for the cached data is set from the Admin > OAT Config page (the default value is 5 minutes). Every time the Group Summary page refreshes, it pulls data from the OAT cache. If the data is the cache is older than the refresh interval on the Admin > OAT Config page, OAT will query the database server to refresh the data in the cache. Otherwise, it will just display what is in the cache. The time that this data was last retrieved from the database server is shown in the Last Updated column. You can bypass the cache and refresh the data directly from a specific server by clicking the Refresh icon for the server.

image

For more information about the OpenAdmin Tool and the new features in version 3.11, go to the OpenAdmin Tool webpage.

- Erika Von Bargen

OAT Version 2.23 Available Now!

cheetahblog Tags:  openadmin oat 2,000 Views

The newest version of OpenAdmin Tool for IDS, Version 2.23, has just been released! Download the new version today to get the enhanced SQL Explorer and version 2.0 of the ER Plugin.



New feature highlights:



The SQL Explorer has been newly redesigned. New features include

  • Filters and search fields for viewing SQL tracing data.
  • Support for different SQL trace levels - global or user - so you can manage what kinds of SQL information are traced for IDS V11.50.xC1 servers.
  • Support for suspending and resuming history tracing, without releasing resources, on IDS V11.50.xC1 servers.


ER Plug-in Version 2.0: Version 2.0 of OAT’s ER plug-in includes a new Replicate Explorer and becomes the first step in supporting ER administration graphically through OAT.





  • The Replicate Explorer now lets you monitor your replicates and replicate sets.
  • Screenshot of the Replicate Explorer



  • The Node Details -> Configuration page now supports the editing and updating of ER configuration parameters






Download OAT Version 2.23 now at https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd



For additional information on OpenAdmin Tool for IDS, including feature details, screenshots and demos, go to www.openadmintool.org.

Erika Von Bargen

[Read More]

ON-Bar whole system parallelize backup/restore

cheetahblog 1 Comment 1,682 Views

Prior to Cheetah,  a whole-system backup (onbar -b-w)  was serial backup of all storage spaces andlogical logs based on a single checkpoint. That time is stored with thebackup information. Theadvantage of using a whole-system backup was  that you canrestore the storage spaces with or without the logical logs.Because the data in all storage spaces is consistent in a whole-systembackup, you do not need to restore the logical logs to make the dataconsistent. Level 0, 1, or 2 backups are supported.

One of the new features of Cheetah is Backup/Restore dbspacesin parallel with whole system functionality .  With parallelbackup , multiple processes run simultaneously  each processbacking up a different dbspace . In most cases, parallel backups complete fasterthan serialbackups, which use only one process.

ONCONFIG parameter  "BAR_MAX_BACKUP" isused for whole system backups/restores as well. TheBAR_MAX_BACKUP parameter specifies the maximum number of parallelprocesses that are allowed for each onbar command. Both UNIX andWindows support parallel backups. Although the database server defaultvalue for BAR_MAX_BACKUP is 4, the onconfig.std value is 0.

To specify parallel backups and restores, including parallel wholesystem backups and restores, set BAR_MAX_BACKUP to a value higher than1. For example, if you set BAR_MAX_BACKUP to 3 and execute anON–Bar command, ON–Bar will spawn the maximum 3 processes concurrently.

If  BAR_MAX_BACKUP to 0, the system creates as manyON–Bar processes as needed. The number of ON–Barprocesses is limited only by the number of storage spaces or the amountof memory available to the database server, whichever is less.

To perform a serial backup or restore, including a serialwhole system backup or restore, set BAR_MAX_BACKUP to 1. 

Also in Cheetah,  storage manager efficiency isimproved  by improvingdbspace ordering .  "Biggest" dbspaces are backed up first and the "smallest"last.Dbspace with most pages used is the "biggest" and will be backed upfirst. This ensures better parallelism, no matter how BAR_MAX_BACKUP isset or how many pages are to be backed up in different dbspaces. Neworder of backup/restore is also effective for non-wholesystembackup/restore. Restore is done in the same order as backup to ease storagemanager's  access to stored objects.

To implement new ordering of dbspaces for backup and restore changes are made to the bar_instance table in the sysutils Database .
New column "ins_backup_order" (integer default 0 not null) is added to the table bar_instance .
 
Also changes are made to ixbar file :
New field with backup sequence integer (Last field in the line ) is added in ixbar file.
Value of the new field is always "0" (zero) for log backup objects.

Even if the BAR_MAX_BACKUP is set to 1 for serial backup,the dbspaces will be backed up using the new dbspace order .

§
Backup Operation
=================
Only 1 checkpoint for all dbspaces - just before backup of rootdbs .
Rootdbs is backed up first, without parallelism (no change here).
Before image processor threads "arcbackup2" are started at this time, one for each dbspace  (more threads running in parallel.)
As each dbspace backup completes, the respective "arcbackup2" thread exits ( less "arcbackup2" threads as backup progresses. )
New order of backup. The order is based on the used-pages count at the start time of backup. Dbspace with most pages used is the "biggest" and will be backed up first.

Restore Operation
================
Not much changed, only it is now parallel.
Rootdbs is restored first, without parallelism (no change here).
Restore dbspaces in same order as they were backed up. Use the value of "ins_backup_order" to determine correct order. If BAR_MAX_BACKUP is changed between backup and restore, the objects will still be restored in the same order, however the timing relative to each other may differ significantly. This could have negative effects on SM performance .

Rashmi Chawak[Read More]

Onbar backup order on Cheetah

cheetahblog 3 Comments 2,037 Views

Onbar backup order on Cheetah

1> Root dbsapce , SBLOBspace, BLOB space, and normal dbspace onbar backup order.
Rootdbspace is always first, then SBLOB space, BLOB space, normal dbspace is the last one.
For example:
Onstat –d output as following:

/home/informix/cheetah:onstat -d

IBM Informix Dynamic Server Version 11.10.UC1 -- On-Line -- Up 00:19:47 -- 44672 Kbytes

Dbspaces
address number flags fchunk nchunks pgsize flags owner name
44cdf7f0 1 0x60001 1 1 2048 N B informix rootdbs
45ca6ea8 2 0x40001 2 1 2048 N B informix ddbs1
45c26ea8 3 0x40001 3 1 2048 N B informix ddbs2
45ca7a00 4 0x48001 4 1 2048 N SB informix sbdbs
45bbeeb0 5 0x60011 5 1 8192 N BB informix bdbs
5 active, 2047 maximum

Note: For BLOB chunks, the number of free pages shown is out of date.
Run 'onstat -d update' for current stats.

Chunks
address chunk/dbs offset size free bpages flags pathname
44cdf950 1 1 0 50000 15421 PO-B /home/informix/cheetah/dsk/rootdbs
45c98e38 2 2 0 5000 4947 PO-B ./dsk/ddbs1
45ca73b0 3 3 0 5000 3449 PO-B ./dsk/ddbs2
45ca7b60 4 4 0 5000 4587 4587 POSB ./dsk/sbdbs
Metadata 360 268 360
45bb0e48 5 5 0 5000 ~1250 1250 POBB ./dsk/bdbs
5 active, 32766 maximum


After onbar –b –w ,and the backup order can be showed in the bar_act.log infor as following:

2007-06-15 04:41:10 5218 5216 /home/informix/cheetah/bin/onbar_d -b -w
2007-06-15 04:41:10 5218 5216 Archive started on rootdbs, sbdbs, bdbs, ddbs2, ddbs1 (Requested Level 0).
2007-06-15 04:41:11 5218 5216 Begin level 0 backup rootdbs.
2007-06-15 04:41:11 5218 5216 Successfully connected to Storage Manager.
2007-06-15 04:41:11 5218 5216 Completed level 0 backup rootdbs (Storage Manager copy ID: 751421824 0).
2007-06-15 04:41:12 5223 5218 Process 5223 5218 successfully forked.
2007-06-15 04:41:12 5223 5218 Begin level 0 backup sbdbs.
2007-06-15 04:41:12 5223 5218 Successfully connected to Storage Manager.
2007-06-15 04:41:12 5224 5218 Process 5224 5218 successfully forked.
2007-06-15 04:41:12 5224 5218 Begin level 0 backup bdbs.
2007-06-15 04:41:12 5224 5218 Successfully connected to Storage Manager.
2007-06-15 04:41:12 5225 5218 Process 5225 5218 successfully forked.
2007-06-15 04:41:12 5225 5218 Begin level 0 backup ddbs2.
2007-06-15 04:41:12 5226 5218 Process 5226 5218 successfully forked.
2007-06-15 04:41:12 5226 5218 Begin level 0 backup ddbs1.

2> Normal dbspace backup order
The onbar –b –w backup on Normal dbsapces doesn’t depend on the order which the dbspace is created but depend on the size is used on that dbsapce.
In the above example, the ddbs2’s usage is bigger than the ddbs1’s usage, the onbar backup’s order is the ddbs2 first and then the ddbs1 without following the create order.

[Read More]

onstat -g pqs

cheetahblog Tags:  diagnostic sql analysis 1 Comment 3,322 Views
In version 10.00 IDS made available a new onstat command. The onstatcommand was onstat -g pqs. For those familiar with the old X-based toolcalled xtree, onstat -g pqs allows you to dynamically track a session'squery plan/tree. The syntax is as follows:

onstat -g pqs {0||<session id>}

The 0 will actually attempt to grab the current step of each query planfor all sessions.

Two considerations

  1. onstat -g pqs only shows all phases that have currentlyprocessed, plus the current phase.
  2. onstat -g pqs only shows active queries, once a query iscomplete the plan is gone.

Below is a sample based on the following query.

SELECT c.customer_num, fname,lname, company, o.order_num
FROM customer c, orders o, items i
WHERE c.customer_num= o.customer_num
AND o.order_num = i.order_num
GROUP BY c.customer_num, o.order_num, fname, lname, company
ORDER BY c.customer_num, lname, fname, company, order_num


Onstat -g pqs output


As you can see an additional drawback is that the tables themselves are not named, but it does clearly show you the query tree as it was executed.

Mark Jamison[Read More]

OpenAdmin Tool for IDS - new version

cheetahblog Tags:  oat openadmin idsadmin 2,805 Views
A new version of OpenAdmin Tool for IDS (formerly known as IDSAdmin) is available to download from the IDS 11.10 Open Beta site (sign-in required). This new version has a simpler installation, new graphics, and many new features.

Please keep in mind that:
  • The product is still listed as IDSAdmin on the Beta download site.
  • The Readme file downloadable from the Beta site is (at the time of writing) the out of date one from March - do not use it, instead unzip the oatids package and refer to the Readme in there.

Here is a copy of the current Readme:



OpenAdmin Tool For IDS - v2.10

- July 2007

OpenAdmin Tool For IDS ( OAT ) , is a PHP based administration consolewhich can be used to administer one or more IBM Informix Dyanamic Server11.10 instances.

Dependencies



OAT requires the following products to be installed:
  • A Webserver (Apache 2.2.3)
  • IBM I-Connect or CSDK (3.00)
  • PHP 5 compiled with PDO, PDO_SQLITE, GD and SOAP enabled. (5.2.2)
  • Informix PDO Module.


Note: the versions in brackets indicate the versions that OAT has been testedwith.

Installation Instructions

1. Install and set up a working web server that has been configured toserve php pages.
(For more information see the NOTES section below.)

2. Update the php configuration file.


  • edit the php.ini
  • add two new lines to the 'extension' section of the configuration fileif they are not present:

    extension=php_pdo_informix.dll
    extension=php_pdo_sqlite.dll

  • modify the parameter memory_total to 256M.


3. Install IBM I-Connect or Client SDK.

4. Install the OpenAdmin Tool For IDS package.
  • Extract the OAT package into your web server document root directory.
  • Examples:
    /usr/local/apache2/htdocs/oat (UNIX/Linux)


    c:\xampp\htdocs\oat (Windows)
5. Change the ownership of the "<OAT>/install" directoryto the user and group that runs the apache ( httpd ) server.
  • Find the user and group that runs the apache ( httpd ) server from the httpd.conf.
  • chown <user>:<group> <OAT>/install ( Unix/Linux )

6. Start the webserver, making sure INFORMIXDIR points to the Client SDKor I-Connect install location in the webserver environment.

7. Launch the OAT installer by visiting the web page:
http://SERVERNAME/LOCATION/install

where:
SERVERNAME = the name of your machine
LOCATION = where you extracted the tar file (e.g. oat )



Follow the instructions in the OAT installation screens, including obtainingan optional Google Maps API key for your domain if required.

Once installation is complete, point your browser at the OAT root URL.From there you can click on Admin and add a new IDS 11.10 Connection tothe default group or create a new group. If using an I-Connect or ClientSDK version prior to 3.0 then for each new connection you add, there needsto be a corresponding SQLHOSTS entry for that connection on the webservermachine.

Once a connection is created the "Get Servers" link from theOAT root page will retrieve the list of connections you have created.


NOTES

Installing an Apache webserver with PHP is not a straight forward task, thankfully there are 3rd party solutions available. A popular choiceis XAMPP.

Using XAMPP



XAMPP is an easy to install Apache distribution containing MySQL , PHPand Perl. XAMPP can be obtainedfrom,
http://www.apachefriends.org/en/xampp.html

The XAMPP version 1.6.2 for Windows contains everything you need to runOAT (with the exception of IBM I-Connect or CSDK ), download and installationinformation is available at
http://www.apachefriends.org/en/xampp-windows.html

XAMPP ( also called as LAMPP ) is also available for Linux, however you will need to download the 'Development Package' in order to compile the Informix PDO Driver. Download and installation information is available at
http://www.apachefriends.org/en/xampp-linux.html

Informix PDO Driver

The following developerWorks article contains useful information to assistwith building an Informix PDO driver:

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0606bombardier/



Guy Bowerman[Read More]

OpenAdmin Tool for IDS XAMPP tutorial updated

cheetahblog Tags:  idsadmin oat 1,669 Views
In March an article on installing IDSAdmin in a XAMPP environment was published on this blog. This article has just been updated by Inge Halilovic with instructions and screen shots for the latest available version of OpenAdmin Tool downloadable from the Free Product Download Site. The new version of OAT has improved graphics, simpler installation, new features such as support for Mach 11 topology, and several bug fixes.

Guy Bowerman[Read More]

OpenAdmin Tool Security - HTTPS and ONSOCSSL

cheetahblog Tags:  https onsocssl encryption openadmin oat security 5,128 Views












The two major ways to secure OAT's network communication is with HTTPS and ONSOCSSL.



HTTPS

  • Encrypts communication between web browsers and OAT's webserver.
  • Uses digital certificates to certify identity of OAT's webserver.
  • OAT webmasters can setup OAT's webserver, so that only trusted users can access OAT
Click here for detailed steps on how to setup HTTPS on OAT




ONSOCSSL
  • Encrypts communication between OAT's webserver and IDS database.
  • Uses IBM Global Security Kit (IBM GSKit is bundled with IDS server)
  • onsocssl is supported on all informix platforms, excluding MacOS.
Click here for detailed steps on how to setup ONSOCSSL on OAT


Leo Chan Wai Hon


[Read More]

OpenAdmin Tool Version 2.24 Available Now!

cheetahblog Tags:  openadmin oat 1 Comment 4,472 Views




 

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
    •    

       

Download OAT Version 2.24 now at https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd

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.


Erika Von Bargen



[Read More]

OpenAdmin Tool Version 2.25 Available Now!

cheetahblog Tags:  oat openadmin 1 Comment 5,612 Views


What's new in OAT 2.25?



  • Schema Manager Plug-in version 1.0
  • Introducing a new plug-in for OAT: theSchema Manager.  Use this plug-infor a dashboard-like of view databases and tables on your IDS databaseserver.  For databases, see stored procedures, functions, sequences,UDTs, privileges, DataBlades, aggregetes, casts, and opclasses from onesingle view.  For tables, views, and synonyms, the Schema Manager showsyou information about its columns, indexes, references, constraints,privileges, UDTs, fragments, and triggers.  The Schema Manager alsoprovides you with ability to drill-down on any of its pods for moredetailed information.

            

    A demo of the new Schema Manager funcationality isavailable here. More enhancements to this plug-in will be coming soon, so staytuned!




  • Enterprise Replication Plug-in version 2.2   
  • The Enterprise Replication plug-in administrationcapability has been expanded to allow the modification ofobjects:

    • Modify a replicate set by adding or deleting replicates
    • Modify a replicate set by changing the replication frequency for the replicates in the set



    Download OAT Version 2.25 now at https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd. And starting following OAT on Twitter: http://twitter.com/OAT4IDS!



    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.


    Erika Von Bargen



[Read More]

OpenAdmin Tool Version 2.27 Has Arrived!

cheetahblog Tags:  openadmin oat 5,143 Views

OpenAdmin Tool Version 2.27 Has Arrived!

What's new in OAT 2.27?

  • UI Translations in 13 languages
  • Brazilian Portuguese, Chinese Simplified, Chinese Traditional, Czech, German, Hungarian, Italian, Japanese, Korean, Polish, Russian, Slovak, and Spanish

     
image
  • Connection Manager Proxy Support
  • The Connection Manager wizard on OAT's MACH page now allows you to configure the Connection Manager as a proxy server when clients connect to Informix data servers from outside a firewall (new to 11.50.xC6).

  • Onstat Utility Support
  • Ever want to run onstat commands remotely? Now you can. OAT 2.27 supports running any onstat command you like directly from your web browser!

     
image


  • Schema Manager Plug-in version 1.2
  • The Schema Manager plug-in arrives with its first installation of 'admin' actions.

    • Create tables, including external tables
    • Load and unload data from external tables (11.50.xC6 only)
    • Monitor the status of load and unload tasks (11.50.xC6 only)
    • Drop tables
    • Truncate tables

    image

    A demo of the new create table and load from external table functionality is available at www.openadmintool.org/oat_227_demo.swf

    More schema management and administration actions to come, so stay tuned!

  • Enterprise Replication Plug-in version 2.4
  • ER plug-in version 2.4 now contains virtually all administration commands available for IDS Enterprise Replication! Besides rounding out the suite of administration commands supported, the focus on the ER plug-in version 2.4 was usability.

    • Improve the performance of the consistency check and synchronization tasks, using WHERE clauses and parallelism options.
    • Change the master replicate server for a replicate.
    • Instiate a template on multiple ER servers simultaneously.
    • Delete ER objects including servers, replicates, replicate sets, and templates. Also delete information about completed check and synchronization tasks.

    Download OAT Version 2.27 today, for free, at https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd.

    More information on OpenAdmin Tool for IDS, including demos, can be found at www.openadmintool.org.

    To post comments or questions about OAT, use the IIUG OAT forum. Feedback is always welcome!

    Erika Von Bargen

OpenAdmin Tool XAMPP Installation on Linux

cheetahblog Tags:  oat idsadmin linux 3 Comments 9,275 Views

OpenAdmin Tool for IDS, the PHP-based administration console for IDS, is now available for downloadfrom the Open Source download site.

Updated 2/14/08 with new recommended PHP and XAMPP versions which are good for Cheetah and Cheetah 2.

Note: This article is based upon the “Installing IDSAdmin in a Windows XAMPP environment” article by Guy Bowerman and Inge Halilovic. The Windows version of this article is available on the IBM IDS Experts Blog.

Installation of OpenAdmin can appear complex - this article provides anexample of one way of installing OpenAdmin. This example uses an XAMPP package on Linux. XAMPP is a bundle of open-source utilities (such as Apache, PHP and PEAR) thatare used by OpenAdmin. Installing XAMPP simplifies the OpenAdmin install,but many other installation and configuration options are possible.

High-level Summary of Install steps
Detailed steps are provided below. This is the high-level sequence of stepsyou will follow:

  1. Install CSDK 3.00.UC1 (or later)
  2. Download and extract the XAMPP 1.6.4 runtime package and development package
  3. Download and extract PDO_INFORMIX 1.1.0
  4. Configure PDO_INFORMIX
  5. Update the php configuration file
  6. Download and extract the OpenAdmin package
  7. Start the Apache web server
  8. Install and configure OpenAdmin
1. Install Informix Client SDK

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.

  • To install, all you need to do is untar both XAMPP packages in the /opt directory. (If you do not want to install directly in the /opt directory, you can create a symbolic link which links /opt/lampp to the directory you choose for installation.)
    tar xvfz xampp-linux-1.6.4.tar.gz –C /opttar xvfz xampp-linux-devel-1.6.4.tar.gz –C /opt
  • Now XAMPP is installed in the /opt/lampp directory

3. Download and extract PDO_INFORMIX

  • Untar the PDO_INFORMIX tar file into the /opt/lampp/lib/php/extensions directory.
    tar xvfz PDO_INFORMIX-1.1.0.tgz –C /opt/lampp/lib/php/extensions



4. Configure PDO_INFORMIX

  • Set INFORMIXDIR in your environment to the directory where Informix Client SDK is installed.
  • Make sure your PATH is set so that the php and phpize executables are picked up from the /opt/lampp/bin directory. If you have another version of PHP installed and your PATH is not set correctly to pick up php from /opt/lampp/bin, you may get errors when executing make for PDO_INFORMIX due to version incompatibility.
  • Important: If you are installing on a Linux-x86_64 machine, you also need to set CFLAGS in your environment to “-m32”. This is because XAMPP for Linux is a 32-bit build and therefore you will need to compile PDO_INFORMIX into a 32-bit shared object file instead the default 64-bit version. This also means that the version of Informix CSDK must be the Linux 32-bit version.
  • To configure the PDO_INFORMIX module, execute the following 5 commands:
    cd /opt/lampp/lib/php/extensions/PDO_INFORMIX-1.1.0/opt/lampp/bin/phpize./configuremakemake install

    Example output for these commands are shown below.

    Sample output after the “/opt/lampp/bin/phpize” command:

    This illustration is a screen capture after executing the /opt/lampp/bin/phpize command.

    Sample output after the “./configure” command:

    This illustration is a screen capture after executing the ./configure command.

    Sample output after the "make” and “make install” commands:

    This illustration is a screen capture after executing the make commands.

  • After executing “make install”, verify that pdo_informix.so was copied into your extension directory /opt/lampp/lib/php/extensions/no-debug-non-zts-20060613/

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:

  1. Locate the “Dynamic Extensions” section in the php.ini file and add the pdo_informix extension.
    extension="pdo_informix.so"

  2. Modify the memory_limit parameter to be at least 256M.
    Memory_limit = 256M

6. Download and Extract the OpenAdmin tar file in the htdocs directory.

  1. Download the OpenAdmin tar file from the Open Source download site to a temporary location.
  2. Create a new directory under /opt/lampp/htdocs to contain the OpenAdmin Tool files (For example: /opt/lampp/htdocs/openadmin)
  3. Untar oatids.tar to this new directory.
  4. Within the OpenAdmin directory, grant write permissions to the conf and install directories and all of their contents.
    cd /opt/lampp/htdocs/openadminchmod ugo+w install conf -R

7. Start the Apache web server.

  1. Verify INFORMIXDIR is set to the Informix CSDK directory.
  2. Start the web server by running /opt/lampp/lampp start as root.

    You should see output similar to the following:

    Starting XAMPP for Linux 1.6.4...XAMPP: Starting Apache with SSL (and PHP5)...XAMPP: Starting MySQL...XAMPP: Another FTP daemon is already running.XAMPP for Linux started.

8. Install and Configure OpenAdmin.

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:

This image is a screen capture of a web browser displaying the OpenAdmin Tool installation screen. It shows that PDO, pdo_informix, and pdo_sqlite PHP modules have been found and are ready for installation.

Enter the base URL for your machine, language defaults, and optionallythe Google maps key for your machine URL:

This image is a screen capture of a web browser showing the OpenAdmin Tool configuration parameters, including the default language to use, the directory for connections database, the BASEURL for links, where everything is installed, the number of seconds to pausewith a redirect, the Google map key, and how often to check server status (in seconds).

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 .

This image is a screen capture of the OpenAdmin tool login screen. The user is required to fill out the following fields: Informix Server, Host Name, Port, Username, and Password.


Once a connection is defined you can return to the main screen and connect:

This image is a screen capture of the main OpenAdmin Tool after a successful login.

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]

OpenAdmin Tool: Use Heat Map Graphs to monitor Extent and Buffer Pool Usage

cheetahblog Tags:  heat pool informix extent oat graph map openadmin buffer 4,721 Views

OpenAdmin Tool: Use Heat Map Graphs to monitor Extent and Buffer Pool Usage

OpenAdmin Tool (OAT) version 3.11 includes new Heat Map graphs to aid in the performance analysis of your Informix database server. View extent and buffer pool usage in a heat map, also known as a treemap graph.

Go to the new Performance Analysis > System Reports > Graphs page to view graphs of the number of extents in databases or the percentage of cached pages in the buffer pools. Use the drop-down controls at the top of the page to choose the type of data to graph (extents or buffer pool) and to filter by a particular database.



Extents Heat Map

Use the Extents graph to monitor the number of extents across your databases. Monitoring extents is important because a large number of extents can impact performance by requiring the database server to spend extra time finding the data.

Use the size and color cues for each object to interpret the extents graph. Each rectangle within a database represents an object, for example a table or an index. The size of the rectangle indicates the relative size of the object. The color indicates the number of extents as indicated by the legend for the graph. Hover over any object in the graph to see the number of extents and the number of total pages used.

If any table or index has a large number of extents, consider increasing the size of the extents to improve performance.

Screenshots 1 and 2 show examples of the Extents Heat Map in OAT.

Screenshot 1: Extents Heat Map for the sysadmin database

image 

Screenshot 2: Extents Heat Map for all databases

image 

 

Buffer Pool Heat Map

Use the Buffer Pool graph to monitor the cached pages in your buffer pool. Both the size of the buffer pool and which table and indexes pages are cached in the buffer pool can have a big impact on the performance of queries against the database server.

Use the size and color cues for each object to interpret the buffer pool graph. Each rectangle within a database represents an object, for example a table or an index. The size of the rectangle indicates the relative size of the object. The color indicates the percentage of pages that are cached as indicated by the legend for the graph. Red indicates a “hot” table or index that has 100% of its pages cached in the buffer pool, whereas blue indicates a “cold” table or index that has 0% of its pages cached. Hover over any object in the graph to see the total rows, total pages, buffered pages, and percentage of pages that are cached for that object.

A higher percentage of cached pages can improve performance. Queries that are run on tables with a high cache percentage are faster than queries that are run on tables with a low cache percentage. If a table has a low percentage of cached pages and queries are slow, consider increasing the size of the buffer pool.

Screenshots 3 and 4 show examples of the Buffer Pool Heat Map in OAT.

Screenshot 3: Buffer Pool Heat Map for the sysadmin database

image 

Screenshot 4: Buffer Pool Heat Map for all databases

 image
 

For more information about the OpenAdmin Tool and the new features in version 3.11, go to the OpenAdmin Tool webpage.

 

- Erika Von Bargen

Optimizer Directives in ANSI Join Queries

cheetahblog Tags:  directives optimizer joins ansi 1,625 Views
In IDS 11.10, all optimizer directives including external directives can be used in queries using ANSI joins.Using
optimizer directives might enable you to force a favorable query execution path thus improving performance.

Here is a description of the different optimizer directives and how they work with ANSI join queries.

Optimization Goal DirectivesFIRST_ROWS, ALL_ROWSDirectives enabled for ANSI join queries
Access Method DirectivesFULL, INDEX, AVOID_INDEX, AVOID_FULL,INDEX_SJ, AVOID_INDEX_SJDirectives enabled for ANSI join queries
Explain-Mode DirectivesEXPLAIN, AVOID_EXECUTEDirectives enabled for ANSI join queries
External DirectivesDirectives enabled for ANSI join queries
Join Method DirectivesUSE_HASH, USE_NL, AVOID_HASH, AVOID_NL, /BUILD, /PROBEDirectives enabled for ANSI Inner Joins
Directives not enabled for ANSI Inner Joins in views
Directives enabled for ANSI Outer Joins that can be transformed into ANSI Inner joins
Directives disabled for all other ANSI Outer Joins
Join Order DirectivesOrderedDirectives enabled for ANSI Inner Joins
Directives enabled for ANSI Left Outer joins if it doesn't conflict with the ordering requirement for Outer Joins
Directives disabled for all other ANSI Outer Joins

Suma Vinod[Read More]

Oracle PL/SQL compatibility in IDS

cheetahblog Tags:  pl/sql exit continue label for loop while 2,457 Views
IDS 11.10 supports some of the PL/SQL syntax thus expanding the capability of IDS. It also facilitates easy migration of applications that use a lot of stored procedures and triggers written in PL/SQL.

This version supports the following syntax:

GOTO statement and Label syntax

Example:
    define x integer;Let x = 0;BEGIN	<<increment_x>>	BEGIN	     LET x = x + 1;	END;

    IF x < 10 THEN GOTO increment_x; END IF;END;
Restrictions on GOTO:
  • A GOTO statement cannot be used in an EXCEPTION block.
  • Labels cannot be defined within an EXCEPTION BLOCK.
  • A label must be unique within a stored procedure.

Loop .. End Loop syntax

Example:
    LOOP   IF credit_rating IS NULL THEN      CONTINUE; (or CONTNUE LOOP)   END IF   IF credit_rating < 3 THEN      EXIT; (or EXIT LOOP)-- exit loop immediately   END IFEND LOOP;

WHILE LOOP… END LOOP
<> WHILE LOOP… END LOOP whloop_label


Example:
    WHILE (i < 10) LOOP   LET i = i + 1 ;   IF  i < 2 THEN      CONTINUE;   END IF   IF i  > 5 THEN      EXIT;    END IFEND LOOP;
You can re-write the above while loop using a label like shown below:
    <<mywhile>>WHILE (i < 10) LOOP   LET i = i + 1 ;   IF  i < 2 THEN      CONTINUE;   END IF   IF i  > 5 THEN      EXIT;    END IFEND LOOP mywhile;

FOR LOOP… END LOOP
<> FOR LOOP… END LOOP for_label


Example:
    FOR i IN (1 TO 5) LOOP   LET i = i +1 ;   IF  i < 2 THEN      CONTINUE;   IF i  > 5 THEN      EXIT;    END IF; END LOOP;

Expression based exit statement.
  • EXIT WHEN <expr>;
  • EXIT loop_label WHEN <expr>; -- Label Based Exit.
This is especially useful in nested loop statments. Here is an example of a nested loop statement using a label and exitwhen statements:
    <<outer>>    LOOP    LET x = x+1;    <<inner>>            for i in ( 1 to 10) LOOP                LET x = x+1;                 EXIT inner WHEN x = 2;                EXIT outer WHEN x > 3;             END LOOP inner;    LET x = x+1;    END LOOP outer;
Please note that for while and for loops you can use both the old and new syntax.

Suma Vinod[Read More]

Overview of IDS LBAC -- Part 1

cheetahblog Tags:  lbac 1,971 Views

This article describes LBAC concept and outlines IDS LBAC solution an implementation of MAC (Mandatory Access Control) for protecting data rows and columns based on security labels.

What is LBAC?

Label-Based Access Control (LBAC) is a means by which a database system can control access to a database object based on security labels. If the security label granted to a user dominates the security label protecting that object, access to the object is authorized. A database object in this context can refer to either a row of data or a table column. Each protected database object is assigned a security label which stores information about the classification (or sensitivity) of the data. Similarly, each database user is assigned a security label that determines which labeled data (rows or columns) he or she can access. LBAC is an implementation of MAC (Mandatory Access Control),which provides a reasonable level of security assurance of sensitive data stored in database.

What is IDS LBAC solution?

IDS LBAC solution is similar to DB2 LBAC solution; some key characteristics are as following:
  1. Offer row level and column level protection, allowing protection of data rows of a table and columns of table.


  2. Support three types (Hierarchical, Horizontal and Tree structured) security label component: ARRAY, SET and TREE respectively.


  3. Ship pre-defined IDSLBAC access rules to govern the access to table row/column protected by security label. Access control enforces the proper IDSLBAC access rules that apply for the security component type for each component that makes up the security label.


  4. Allow flexible definition of security label components that make up a security label, which allows the security label structure to be compatible with US government as well as other application domain which require different label structure not necessarily compliant to traditional MLS security label structure and access rules. Support a security label to be made up to 16 components of any type.


  5. Provides means to bypass LBAC access rules through granting user exemption.


  6. Provides functions for manipulating security label. SECLABEL_BY_COMP(), SECLABEL_BY_NAME(), SECLABEL_TO_CHAR().



In part2 of LBAC overview, basic LBAC concepts and a simple user scenario will be covered.

Jihong Ma[Read More]

Overview of LBAC - Part2

cheetahblog Tags:  lbac 1,996 Views

What are the basic IDS LBAC concepts?


1. Security label component

Security label component is a new database entity that can be created, dropped, altered and renamed. It is the building block for security labels. A security label is composed of one or more security label components. For ALTER SECURITY LABEL COMPONENT, we only support adding security label component elements to existing security label component.

There are three types of security label components: arrays, sets, and trees.

ARRAY: An array represents an ordered set. In an array, the order in which the elements appear is important because it denotes the degree of sensitivity of the data. The rank of the first element is higher than the rank of the second element, and so on. There can be a maximum of 64 security label component element values in the array.

For example:
CREATE SECURITY LABEL COMPONENT levels ARRAY ['TOP SECRET', 'SECRET', 'UNCLASSIFIED'];
ALTER SECURITY LABEL COMPONENT levelsARRAY ['CONFIDENTIAL' BEFORE ‘UNCLASSIFIED’];

SET: A set is a collection of elements where the order in which those elements appear is not important. There can be a maximum of 64 unique values in the set.

For example:
CREATE SECURITY LABEL COMPONENT compartments SET {'Sales', 'HR', 'R&D', ‘Tech Support’};
ALTER SECURITY LABEL COMPONENT compartments SET {‘Marketing’, ‘Finance’};

TREE: A tree represents a hierarchy and is introduced to support the "group" concept that Oracle supports. Trees can be used to represent organizational charts and to identify departments within the organization that own the data. There can be a maximum 64 security label component elements in the tree.

For example:
CREATE SECURITY LABEL COMPONENT groupsTREE ('Worldwide' ROOT, 'Europe' UNDER 'Worldwide', 'North America' UNDER ' Worldwide ', 'Asia' UNDER 'Worldwide', 'China' UNDER 'Asia', 'USA' UNDER 'North America');
ALTER SECURITY LABEL COMPONENT groups TREE (‘France’ UNDER ‘Europe’, ‘Canada’ UNDER ‘North America’);

2. Security Policy

A security policy is a new database entity that can be created, dropped and renamed. The security policy defines the set of security label components that make up a security label. The security policy also specifies the access rules that IDS uses to determine whether a user who holds a security label L1 can access a data row or a column protected with a security label L2. A table can have maximum of one security policy attached to it and a policy can have a maximum of 16 security label components.

For example:
CREATE SECURITY POLICY secpolicy1COMPONENTS level, compartments, groups WITH IDSLBACRULES;

3. Access Rule

There are access rules for READ access and WRITE access for each type of security label component made up the security policy. Read access rules are applied by IDS when a user attempts to read (SELECT) a labeled data row. IDS applies the write access rules when a user attempts to insert, update or delete a data row. IDS will ship with a predefined set of read and write access rules. These rules are collectively called IDSLBACRULES and they can be divided into two categories as follows.

READ ACCESS RULE

The read access rules, called IDSLBACREAD, apply when data is retrieved. Data is retrieved on SELECT, UPDATE and DELETE operations. They can be summarized as follows:

IDSLBACREADARRAY: Each array component of the user security label must be greater than or equal to the array component of the data row security label i.e. the user can only read data at or below his/her level.
IDSLBACREADTREE: Each tree component of the user security label must include at least one of the elements in the tree component of the data row security label (or the ancestor of one such element).
IDSLBACREADSET: Each set component of the user security label must include the set component of the data row security label.

WRITE ACCESS RULE

The write access rules, called IDSLBACWRITE, apply for INSERT, UPDATE and DELETE operations. They can be summarized as follows:

IDSLBACWRITEARRAY: Each array component of the user security label must be equal to the array component of the data row security label i.e. the user can write data only at his/her level.
IDSLBACWRITETREE: Each tree component of the user security label must include at least one of the elements inthe tree component of the data row security label (or the ancestor of one such element).
IDSLBACWRITESET: Each set component of the user security label must include the set component of the data row security label.

4. Security Labels

A security label is a new database entity that can be created, dropped and renamed. A security label is always associated with a security policy which defines the set of components that make up a security label. There are three types of security labels:

User security label: A security label that is granted to a database user. A user can have at the most two labels per policy: one for read access and one for write access.

Row security label: A security label tagged with a data row of a database table.

Column security label: A security label associated with a column of a database table.

For example:
CREATE SECURITY LABEL secpolicy1.mylabel COMPONENT level 'TOP SECRET', COMPONENT compartments 'Marketing', COMPONENT groups 'Europe', 'north America';

5. Exemption

Exemption is a means to allow some database users to bypass the label access rules. The administrator can grant a database user an exemption to bypass one or more rules in a particular security policy. The following exemptions will be supported:

  1. An exemption to bypass one or more of the IDSLBACREAD rules

  2. An exemption to bypass one or more of the IDSLBACWRITE rules

  3. An exemption to bypass all read and write access rules


Note that the IDSLBACWRITEARRAY rule can be thought of as being two different rules combined. One prevents writing to data that is higher than user’s level (write-up) and the other prevents writing to data that is lower than user’s level (write-down). When granting an exemption to this rule one can exempt the user from either of these rules or from both.

For example:
GRANT EXEMPTION ON RULE IDSLBACWRITEARRAY WRITEDOWN FOR Secpolicy1 TO usrA, usrB;

6. Protected Table

A protected table is a database table to which a label security policy has been attached. There are two types of protection granularity supported:

Row level granularity: A database table can be marked as protected with row level granularity during CREATE TABLE or ALTER TABLE by attaching a security policy to such table and by specifying the column where the row security label will be stored; this column is referred as row label column, and the row label column is always defined as type of IDSSECURITYLABEL, which is a build-in distinct type used specifically for row label column. The security policy attached to the database table determines the type of the security label used to protect a data row, and the access rules that will govern access to labeled data rows in that table. The predefined IDSLBACRULES are the supported access rules that are essentially the same as DB2LBACRULES.

Column level granularity: A database table can be marked as protected with column level granularity during CREATE TABLE or ALTER TABLE by attaching a security policy to such table and by attaching a security label to one or more columns of that table. When a column is associated with a security label, that column is referred to as a protected column. The security policy attached to the database table determines the type of the security label used to protect a column, and the predefined IDSLBACRULES access rules that will govern access to protected columns of that table.

A protected table can also be defined with both row and column level granularities. When a protected table is accessed, column level access control is enforced before row level access control.

Example to create table with both row and column level protection
CREATE TABLE T1 (seclabel IDSSECURITYLABEL, employeeId int, SSN char (9) COLUMN SECURED WITH mylabel)SECURITY POLICY Secpolicy1;

7. Protected Column

A protected column is a column to which a security label has been attached. Users may or may not have the right to access such column depending on the security labels granted to them. When an SQL statement that refers to a protected column is submitted, IDS verifies whether the user is authorized to access that column.

IDS applies the rules above to compare the security label associated with the protected column and the security labels associated with the user. If the user cannot access that column in that mode (read or write) then an error is returned. Otherwise, the statement proceeds as usual.

For example:
CREATE TALBE T2 (salary float COLUMN SECURED WITH mylabel, name varchar (20)) security policy Secpolicy1;


What is the new database security administrator DBSECADM role?

IDS LBAC introduces a new database security administrator (DBSECADM) role. This role is required to manipulate LBAC related objects. This role is a server level role and can only be granted by a database server administrator (DBSA). The DBSECADM performs all the security related administration. The responsibilities include:
  1. Create, drop, alter and rename security label components

  2. Create, drop and rename security policies

  3. Create, drop and rename security labels

  4. Attach policies to tables

  5. Grant and revoke security labels

  6. Grant and revoke LBAC rule exemptions

  7. Change user using set session authorization

Example to grant a user with DBSECADM role:

GRANT DBSECADM TO USER user1;


What are security label scalar functions?

Security label scalar functions are introduced by LBAC feature to manipulate security labels during INSERT, UPDATE and SELECT operation.

SECLABEL_BY_COMP(): A built-in function that can be used in insert and update operations toprovide the row security label of a data row by providing its individual components as opposed toproviding its name.
SECLABEL_BY_NAME(): A built-in function that can be used in insert and update operations toprovide the row security label of a data row by providing its name as opposed to its individualcomponents.
SECLABEL_TO_CHAR(): A built-in function that can be used in select operations that retrieve therow security label column. The function returns the row security label by giving the details of itsindividual components as opposed to its name. This function can be thought of as the reverse of SECLABEL_BY_COMP.

Examples of security label functions

INSERT INTO T1 VALUES (SECLABEL_BY_COMP('Secpolicy1', 'Top Secret:Marketing:Europe’), 1, '100200300');
INSERT INTO T1 VALUES (SECLABEL_BY_NAME('Secpolicy1', 'mylabel'), 2, '200300600');
SELECT SECLABEL_TO_CHAR('Secpolicy1', seclabel), employeeId, SSN FROM T1;

Usage scenario:

This simple usage scenario will serve as an example to demonstrate how to perform LBAC setup on your database to protect your sensitive data.

1. DBSECADM creates the security label components, security policy, and security label.

CREATE SECURITY LABEL COMPONENT level ARRAY ['TOP SECRET', 'SECRET', 'CONFIDENTIAL', 'UNCLASSIFIED'];
CREATE SECURITY LABEL COMPONENT compartments SET {'Sales', 'HR', 'R&D', ‘Tech Support’};
CREATE SECURITY LABEL COMPONENT groups TREE ('Worldwide' ROOT, 'Europe' UNDER 'Worldwide', 'North America' UNDER ' Worldwide ', 'Asia' UNDER 'Worldwide', 'China' UNDER 'Asia', 'USA' UNDER 'North America');
CREATE LABEL SECURITY POLICY Secpolicy1COMPONENTS level, compartments, groupsWITH IDSLBACRULES;
CREATE SECURITY LABEL Secpolicy1.HR COMPONENT level 'TOP SECRET',COMPONENT compartments 'HR',COMPONENT groups 'Worldwide';

2. DBSECADM grants security label to user Nancy who is an employee from HR department.

GRANT SECURITY LABEL Secpolicy1.HR FOR ALL ACCESS TO USER Nancy;

3. DBSECADM creates a protected table and attaches the label security policy to the table:

CREATE TABLE T1 (seclabel IDSSECURITYLABEL, employeeId int, SSN char(9) COLUMN SECURED WITH HR) SECURITY POLICY Secpolicy1;

4. User Nancy try to access the table.

INSERT INTO T1 (employeeId, SSN) VALUES (6, '123456789');



This statement will succeed and the seclabel column is inserted with the default valueof Nancy’s write label, in this case it is label HR.

SELECT employeeId, SSN from T1;

This statement will return one row Nancy just inserted. If the same SELECT statement is executed by user Bob and Bob has not been granted any label for Secpolicy1 yet, no rows will be returned.

DELETE from table T1 where employeeId = 6;

If the DELETE statement is executed by Nancy, one row (inserted by Nancy) will be deletedfrom the table, no row will be deleted if it is executed by Bob.


More practical usage scenarios of LBAC will be covered in subsequent LBAC blog entries to illustrate the power of LBAC on data access control.

Jihong Ma[Read More]

Polling Performance and IDS 7.x to IDS 10.x Upgrade

cheetahblog Tags:  performance polling pollthread configuration fastpoll 1 Comment 3,374 Views

Introduction:

This article describes a customer’s experience with their poll thread configuration while upgrading from IDS 7.31.FD9 to IDS 10.00.FC6. This particular upgrade was related to their busiest IDS server running on an HP Superdome. Typically, one could observe upwards of at least 3000 short-lived soctcp connections on this system.

Original IDS 7.31 Configuration:

Key configuration settings that were active in the IDS 7.31 environment were initially used after upgrading to IDS 10.00:

  • 23 CPU VPs
  • 20 poll threads running on NET VPs
  • multiple server aliases

Optimal IDS 10.00 Configuration:

An optimal configuration was ultimately determined and incorporated the following configuration settings in the IDS 10.00 server:

  • 23 CPU VPs
  • a handful (3-5) of poll threads running on NET VPs
  • enable new IDS 10.00 ONCONFIG parameter, FASTPOLL
  • multiple server aliases

Relevant Testing:

Stress testing supportive of this optimal configuration was conducted on a 16-processor/32-core HP server using the latest IDS 7.31 and IDS 10.00 64 bit products. The testing involved a multi-threaded ESQL/C application that would spawn 3000 threads over 3 server aliases. Each thread would connect to the server, complete a small amount of read-only work and disconnect from the server 30 times. These 90,000 total connections mimicked the customer’s workload and considered poll threads running both on CPU VPs (inline) and on NET VPs against servers configured with 23 CPU VPs. The following chart shows results from the stress testing that were considered for the optimal customer configuration:

Stress Testing Results



Jeff Laube[Read More]