If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

As you probably know, Windows PowerShell is the new command shell and scripting language that provides a command line environment for interactive exploration and administration of computers. In addition, it provides an opportunity to script these commands so that we can schedule and run these scripts multiple times.

Windows PowerShell depends on .NET framework 2.0.

SQL Server Management Objects, known as SMO, is an object model for SQL Server and its configuration settings. SMO-based applications use .NET Framework languages to program against this in-memory object model, rather than sending Transact-SQL (T-SQL) commands to SQL Server to do so.

In this article series, I am going to illustrate the power of Windows PowerShell in conjunction with SQL Server 2005.

Part I of this series is going to illustrate how to install and use a simple PowerShell command and a simple SMO command.

Assumption

a.  The machine you use already has .NET 2.0 installed

b.  The machine you use already has SQL Server 2005 client installed with the latest service pack

Download and Install Microsoft PowerShell

a.  Download Microsoft PowerShell “WindowsXP-KB926139-x86-ENU.exe” from http://download.microsoft.com

b.  Install PowerShell

Step 1: Double click on the “WindowsXP-KB926139-x86-ENU.exe’ executable. [Refer Fig 1.0]

Fig 1.0

Step 2: Click “Run”. [Refer Fig 1.1]

Fig 1.1

Step 3: Click “Next”. [Refer Fig 1.2]

Fig 1.2

Step 4: Select the option “I agree”. [Refer Fig 1.3]

Fig 1.3

Step 5: Watch the progress of installation. Refer Fig 1.4

Fig 1.4

Step 6: Click Finish. [Refer Fig 1.5]

Fig 1.5

Launch PowerShell

There are few ways to launch PowerShell. One method is to go to the command prompt and type the following command. [Refer Fig 1.6]

PowerShell

Fig 1.6

After a short pause, the PowerShell prompt appears. [Refer Fig 1.7]

Fig 1.7

Alternatively, you can start PowerShell by selecting Programs-Windows PowerShell 1.0-Windows PowerShell. [Refer Fig 1.8]

Fig 1.8

Command Help

Inside Windows PowerShell, you can access the command list by typing the command:

Get-command

This displays all of the commands available in PowerShell. [Refer Fig 1.9]

Add-Content Add-History Add-Member Add-PSSnapin Clear-Content Clear-Item Clear-ItemProperty Clear-Variable Compare-Object ConvertFrom-SecureString Convert-Path ConvertTo-Html ConvertTo-SecureString Copy-Item Copy-ItemProperty Export-Alias Export-Clixml Export-Console Export-Csv ForEach-Object Format-Custom Format-List Format-Table Format-Wide Get-Acl Get-Alias Get-AuthenticodeSignature Get-ChildItem Get-Command Get-Content Get-Credential Get-Culture Get-Date Get-EventLog Get-ExecutionPolicy Get-Help Get-History Get-Host Get-Item Get-ItemProperty Get-Location Get-Member Get-PfxCertificate Get-Process Get-PSDrive Get-PSProvider Get-PSSnapin Get-Service Get-TraceSource Get-UICulture Get-Unique Get-Variable Get-WmiObject Group-Object Import-Alias Import-Clixml Import-Csv Invoke-Expression Invoke-History Invoke-Item Join-Path Measure-Command Measure-Object Move-Item Move-ItemProperty

New-Alias New-Item New-ItemProperty New-Object New-PSDrive New-Service New-TimeSpan New-Variable Out-Default Out-File Out-Host Out-Null Out-Printer Out-String Pop-Location Push-Location Read-Host Remove-Item Remove-ItemProperty Remove-PSDrive Remove-PSSnapin Remove-Variable Rename-Item Rename-ItemProperty Resolve-Path Restart-Service Resume-Service Select-Object Select-String Set-Acl Set-Alias Set-AuthenticodeSignature Set-Content Set-Date Set-ExecutionPolicy Set-Item Set-ItemProperty Set-Location Set-PSDebug Set-Service Set-TraceSource Set-Variable Sort-Object Split-Path Start-Service Start-Sleep Start-Transcript Stop-Process Stop-Service Stop-Transcript Suspend-Service Tee-Object Test-Path Trace-Command Update-FormatData Update-TypeData Where-Object Write-Debug Write-Error Write-Host Write-Output Write-Progress Write-Verbose Write-Warning

Fig 1.9

A simple SMO in PowerShell

Let us query the sys.sydatabases of the SQL Server instance “SQLEXPRESS” from the host machine “HOME” using PowerShell and SMO.

Step 1

Go to the command prompt. Start – run – cmd

Step 2

Start PowerShell by typing the following command.

PowerShell

Step 3

Execute the following command, one by one, as shown below.

[reflection.assembly]::LoadWithPartialName(”Microsoft.SqlServer.Smo”)
$Server = new-object (”Microsoft.SqlServer.Management.Smo.Server”) “HOME\SQLEXPRESS”
foreach($database in $Server.databases) {$database.name}

You will see the results as shown below. [Refer Fig 2.0]

Fig 2.0

SMO Members

In order to display all of the members related to the object $Server, execute the following command as shown.

Step 1

Go to the command prompt. Start – run – cmd

Step 2

Start PowerShell by typing the following command:

PowerShell

Step 3

Execute the following commands, one by one, as shown below.

[reflection.assembly]::LoadWithPartialName(”Microsoft.SqlServer.Smo”)
$Server = new-object (”Microsoft.SqlServer.Management.Smo.Server”) “HOME\SQLEXPRESS”
$server | get-member

You will see the following results. [Refer Fig 2.1]

Alter AttachDatabase CompareUrn DeleteBackupHistory Deny DetachDatabase DetachedDatabaseInfo EnumAvailableMedia EnumCollations EnumDatabaseMirrorWitnessRoles EnumDetachedDatabaseFiles EnumDetachedLogFiles EnumDirectories EnumErrorLogs EnumLocks EnumMembers EnumObjectPermissions EnumPerformanceCounters EnumProcesses EnumServerAttributes EnumServerPermissions EnumStartupProcedures EnumWindowsDomainGroups EnumWindowsGroupInfo EnumWindowsUserInfo Equals GetActiveDBConnectionCount GetDefaultInitFields GetHashCode GetPropertyNames GetSmoObject GetType get_ActiveDirectory get_BackupDevices get_Configuration get_ConnectionContext get_Credentials get_Databases get_DefaultTextMode get_Endpoints get_Events get_FullTextService get_Information get_InstanceName get_JobServer get_Languages get_LinkedServers get_Logins get_Mail get_Name get_NotificationServices get_Properties get_ProxyAccount get_ReplicationServer get_Roles get_ServiceMasterKey get_Settings

get_State get_SystemDataTypes get_SystemMessages get_Triggers get_Urn get_UserData get_UserDefinedMessages get_UserOptions Grant Initialize IsDetachedPrimaryFile IsWindowsGroupMember KillAllProcesses KillDatabase KillProcess PingSqlServerVersion ReadErrorLog Refresh Revoke SetDefaultInitFields set_DefaultTextMode set_UserData ToString ActiveDirectory BackupDevices Configuration ConnectionContext Credentials Databases DefaultTextMode Endpoints Events FullTextService Information InstanceName JobServer Languages LinkedServers Logins Mail Name NotificationServices Properties ProxyAccount ReplicationServer Roles ServiceMasterKey Settings State SystemDataTypes SystemMessages Triggers Urn UserData UserDefinedMessages UserOptions

Fig 2.1

Conclusion

As explained in the beginning of this article, this series is going to illustrate the power of Windows PowerShell in conjunction with SQL Server 2005. Part I of this series illustrated how to install and use a simple PowerShell command and a simple SMO command.

—

by Muthusamy Anantha Kumar

• 2 Comments

In this article, we discussed what and how to use a subquery in a T-SQL statement. In this article I will expand on this subject by discussing correlated subqueries. I will explain what a correlated subquery is, and show a number of different examples on how to use a subquery in a T-SQL statement.

What is a Correlated Subquery?

A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself.

A correlated subquery will be executed many times while processing the T-SQL statement that contains the correlated subquery. The correlated subquery will be run once for each candidate row selected by the outer query. The outer query columns, referenced in the correlated subquery, are replaced with values from the candidate row prior to each execution. Depending on the results of the execution of the correlated subquery, it will determine if the row of the outer query is returned in the final result set.

Using a Correlated Subquery in a WHERE Clause

Suppose you want a report of all “OrderID’s” where the customer did not purchase more than 10% of the average quantity sold for a given product. This way you could review these orders, and possibly contact the customers, to help determine if there was a reason for the low quantity order. A correlated subquery in a WHERE clause can help you produce this report. Here is a SELECT statement that produces the desired list of “OrderID’s”:

select distinct OrderId
  from Northwind.dbo.[Order Details] OD
  where
    Quantity &ltl; (select avg(Quantity) * .1
                      from Northwind.dbo.[Order Details]
                      where OD.ProductID = ProductID)

The correlated subquery in the above command is contained within the parenthesis following the greater than sign in the WHERE clause above. Here you can see this correlated subquery contains a reference to “OD.ProductID”. This reference compares the outer query’s “ProductID” with the inner query’s “ProductID”. When this query is executed, the SQL engine will execute the inner query, the correlated subquery, for each “[Order Details]” record. This inner query will calculate the average “Quantity” for the particular “ProductID” for the candidate row being processed in the outer query. This correlated subquery determines if the inner query returns a value that meets the condition of the WHERE clause. If it does, the row identified by the outer query is placed in the record set that will be returned from the complete T-SQL SELECT statement.

The code below is another example that uses a correlated subquery in the WHERE clause to display the top two customers, based on the dollar amount associated with their orders, per region. You might want to perform a query like this so you can reward these customers, since they buy the most per region.

select CompanyName, ContactName, Address,
       City, Country, PostalCode from Northwind.dbo.Customers OuterC
where CustomerID in (
select top 2 InnerC.CustomerId
     from Northwind.dbo.[Order Details] OD join Northwind.dbo.Orders O
               on OD.OrderId = O.OrderID
          join Northwind.dbo.Customers InnerC
               on O.CustomerID = InnerC.CustomerId
     Where Region = OuterC.Region
     group by Region, InnerC.CustomerId
     order by sum(UnitPrice * Quantity * (1-Discount)) desc
     )
order by Region

Here you can see the inner query is a correlated subquery because it references “OuterC”, which is the table alias for the “Northwind.DBO.Customer” table in the outer query. This inner query uses the “Region” value to calculate the top two customers for the region associated with the row being processed from the outer query. If the “CustomerID” of the outer query is one of the top two customers, then the record is placed in the record set to be returned.

Correlated Subquery in the HAVING Clause

Say your organizations wants to run a yearlong incentive program to increase revenue. Therefore, they advertise to your customers that if each order they place, during the year, is over $750 you will provide them a rebate at the end of the year at the rate of $75 per order they place. Below is an example of how to calculate the rebate amount. This example uses a correlated subquery in the HAVING clause to identify the customers that qualify to receive the rebate. Here is my code for this query:

select C.CustomerID, Count(*)*75 Rebate
  from Northwind.DBO.Customers C
         join
       Northwind.DBO.Orders O
         on c.CustomerID = O.CustomerID
  where Datepart(yy,OrderDate) = ‘1998′
  group by C.CustomerId
  having 750 < ALL(select sum(UnitPrice * Quantity * (1-Discount))
           from Northwind.DBO.Orders O
                  join
                Northwind.DBO.[Order Details] OD
                  on O.OrderID = OD.OrderID
           where CustomerID = C.CustomerId
             and Datepart(yy,OrderDate) = ‘1998′
           group by O.OrderId
            )

By reviewing this query, you can see I am using a correlated query in the HAVING clause to calculate the total order amount for each customer order. I use the “CustomerID” from the outer query and the year of the order “Datepart(yy,OrderDate)”, to help identify the Order records associated with each customer, that were placed the year ‘1998′. For these associated records I am calculating the total order amount, for each order, by summing up all the “[Order Details]” records, using the following formula: sum(UnitPrice * Quantity * (1-Discount)). If each and every order for a customer, for year 1998 has a total dollar amount greater than 750, I then calculate the Rebate amount in the outer query using this formula “Count(*)*75 “.

SQL Server’s query engine will only execute the inner correlated subquery in the HAVING clause for those customer records identified in the outer query, or basically only those customer that placed orders in “1998″.

Performing an Update Statement Using a Correlated Subquery

A correlated subquery can even be used in an update statement. Here is an example:

create table A(A int, S int)
create table B(A int, B int)

set nocount on
insert into A(A) values(1)
insert into A(A) values(2)
insert into A(A) values(3)
insert into B values(1,1)
insert into B values(2,1)
insert into B values(2,1)
insert into B values(3,1)
insert into B values(3,1)
insert into B values(3,1)

update A
  set S = (select sum(B)
             from B
             where A.A = A group by A)

select * from A

drop table A,B

Here is the result set I get when I run this query on my machine:

A           S
———– ———–
1           1
2           2
3           3

In my query above, I used the correlated subquery to update column A in table A with the sum of column B in table B for rows that have the same value in column A as the row being updated.

Conclusion

Let me summarize. A subquery and a correlated subquery are SELECT queries coded inside another query, known as the outer query. The correlated subquery and the subquery help determine the outcome of the result set returned by the complete query. A subquery, when executed independent of the outer query, will return a result set, and is therefore not dependent on the outer query. Where as, a correlated subquery cannot be executed independently of the outer query because it uses one or more references to columns in the outer query to determine the result set returned from the correlated subquery. I hope that you now understand the different of subqueries and correlated subqueries, and how they can be used in your T-SQL code.

—

by Gregory A. Larsen

• 0 Comments

• LiMo Foundation: Welcome - Motorola, NEC, NTT DoCoMo, Panasonic Mobile Communications, Samsung Electronics, and Vodafone established the LiMo Foundation to develop the Foundation Platform, a Linux-based, open mobile communication device software platform
• Erlang, the next Java - Erlang is going to be a very important language. It could be the next Java. Its main problem is that there is no big company behind it
• Judge Says Unix Copyrights Rightfully Belong to Novell - New York Times - In a decision that may finally settle one of the most bitter legal battles surrounding software widely used in corporate data centers, a federal judge ruled Friday afternoon that Novell, and not SCO, is the rightful owner of the copyrights for Unix OS.
• NIST endorses Microsoft’s Open XML in upcoming vote | Tech news blog - CNET News.com - The National Institute of Standards and Technology (NIST) is backing Microsoft’s effort to certify Office Open XML as an international standard.
• How well do you know prototype? - Here, I’ve collected most common use cases that do NOT use all of prototype’s capabilities and their simple solutions. I hope this will be a basic checklist to go through when developing for your next project
• Learning JavaFX Script, Part 1: An Introduction to JavaFX Script for Java Programmers - The JavaFX Script programming language (hereinafter referred to as JavaFX) is a declarative, statically typed scripting language from Sun. Because JavaFX Script is statically typed, it also has the same code structuring, reuse, and encapsulation features
• Real Web 2.0: Quick and dirty Web applications with bookmarklets - Bookmarklets are an important part of the Web 2.0 landscape, and they exemplify the qualities of Web 2.0 technology I tend to focus on in this column: transparency and openness.
• WebLogic Server Virtual Edition Launcher Appliance - WLS-VE is a combination of a Java application server (WebLogic Server) with a Java Virtual Machine that works with hypervisor software and provides only the set of operating system features that WebLogic Server needs to offer its full range of services.
• Arvind Jain’s Blog: WebLogic Server Virtual Edition (WLS-VE) goes GA - With a single command, WLS instances can quickly be deployed onto VMware ESX-enabled resource pools, fully integrated and tuned to run within their own dedicated virtual machines… all without any 3rd party operating systems.
• yav - Javascript form validation tool - Yav is a SIMPLE, POWERFUL and CUSTOMIZABLE javascript-based form validation tool.
• New elements in HTML 5 - HTML 5 introduces new elements to HTML for the first time since the last millennium. New structural elements include aside, figure, and section. New inline elements include time, meter, and progress. New embedding elements include video and audio.
• Download Query Express - Query Express is a simple Query Analyzer look-alike, but being small and free it can be run where the SQL Server client tools are not installed or licensed. This makes it especially useful as a query tool for MSDE and SQL Express. It also connects to Orac

• 0 Comments

• The 90th percentile: MyFaces: The emperor has no clothes - My last project is going into production in a couple of weeks, and it has been implemented using JSF. I started with JSF in good faith: it should be stable by now, it is blessed by Sun and included as the de facto web framework in JEE 5.
• James Ward’s Blog ? Blog Archive ? My Recent Flex & Apollo Adventures - A while back Bruce Eckel and I recorded a screencast of us building a Flex application with Hibernate and XFire on the backend. I finally got around to packaging the code for that demo. You can get it from SourceForge.
• rakaz - Make your pages load faster by combining and compressing javascript and css files - Thanks to a small PHP script and some clever URL rewriting I now have an easy to maintain method to speed up the loading of pages that use many or large css and javascript files.
• Vitamin Features ? Serving JavaScript Fast - The next generation of web apps make heavy use of JavaScript and CSS. We?ll show you how to make those apps responsive and quick.
• lightWindow - Another decent lightbox Javascript library (via Ajaxian) - After researching every single modal window, lightbox, slimbox, etc out there nothing fit the bill. Granted some of them were very nice but only fit a specific purpose
• Dynamic languages: More than just a quick fix | InfoWorld | Analysis | 2007-04-16 | By Andrew Binstock - IT’s rise to prominence as a core competence that delivers competitive advantage has been accompanied by a dramatic increase in the number of software development projects it must complete
• Translation From PR-Speak to English of Selected Portions of Rails Developer David Heinemeier Hansson?s Response to Alex Payne?s Interview [dive into mark] - LAUGH OUT LOUD funny take from Mark Pilgrim, John Gruber style.
• Wordpress Performance: Why My Site Is So Much Faster Than Yours by Elliott Back - There?s no good reason for Wordpress or your site to be slow, except your own negligence. Cache everything. Monitor performance
• Tim Sneath : Introducing Microsoft Silverlight - Silverlight (previously codenamed “WPF/E”) is a lightweight subset of XAML for building rich media experiences on the web.
• Java Community News - BEA Releases JRockit R27.2 with Java 6 Support - BEA’s latest JVM release, JRockit R27.2, is the first implementation of the Java 6 VM. In addition to providing full Java 6 support, the latest JRockit VM includes many-fold performance improvements, especially for applications with short-lived objects.
• Enomalism : XEN Virtualized Server Management Console: Amazon EC2 Migration - The Enomalism Elastic migration module is a migration tool kit for the management and migration of virtual images between your local xen based enomalism environment and the remote Amazon Elastic Compute Cloud Environment.
• Everybody Hates Don Imus - Frank Rich’s brilliant column - Even in that short span, there?s been an astounding display of hypocrisy, sanctimony and self-congratulation from nearly every side of the debate
• Dev2Dev Online: Open Source and BEA - BEA believes in open source. We believe a blended strategy for application development and deployment?combining the best of open source and commercial software?provides important freedom and flexibility not available through all-or-nothing approaches
• Web Worker Daily ? Blog Archive Web Worker Emergency Survival Kit ? - Over the years, I?ve accumulated a variety of tools that don?t take up much space but that come in handy when an emergency comes along. On the average day, I don?t need any of these - but when I do, I?m happy to have them. Here are my suggestions

• 0 Comments

General

• Is product x (or version y of product x) still supported? Are there plans to stop supporting it? See the Informix Product Lifecycle document for descriptions of the different support categories and an Excel- or Acrobat- format list of products.
• What patches do I need for platform x?The release notes (in $INFORMIXDIR/release) contain a list of patches which have been determined by Informix Product Development to be necessary for running the product. This list is created prior to release of the product, so any new patches identified after the release of the product (e.g. as a result of specific customer problems) will not be listed in the release notes, but will probably be listed in the release notes of the next version. Therefore, if you are looking for “the definitive list of patches that should be installed”, there is no such thing. Informix generally recommends that the customer consult their OS vendor for a list of recommended patches.

  You may be able to find more helpful information at the following vendor sites:

  • SunSolve Online
  • HP Electronic Support Center (US, Canada, Asia-Pacific, and Latin-America)
  • HP Electronic Support Center (Europe)
• How can I tell what OS patches are already installed?
  • Solaris: Run “/bin/showrev -p” or see /var/sadm/patch
  • HP: Run “/usr/sbin/swlist” as root

This varies by Operating System.

• What documentation does Informix have on Year 2000 compliance?

Which versions support DBCENTURY?
Do I need to upgrade my engine and/or my front-end for DBCENTURY?See Informix’s webpages Year 2000 and Informix Products and Year 2000 Support in Client API Products for information on Year 2000 compliance.

Note that all Informix products are “Year 2000-compliant” in that they have always stored dates with the complete 4-digit century (actually, as an integer representing the number of days from December 31, 1899). What DBCENTURY adds is the ability to have 2-digit years expanded automatically to the appropriate century, as determined by the user.

2-digit years are expanded to 4-digits when the string is converted to a DATE type. If you are using 4GL, for example, the statement:

 LET date_variable = "9/9/99"

causes the expansion to be done by 4GL before assigning the value to date_variable. However, the statement:

 INSERT INTO orders (order_date) VALUES ("9/9/99")

sends the string to the engine and causes the expansion to be done by the engine. Whether you need to recompile your 4GL applications with a DBCENTURY-aware version of 4GL, or whether it is sufficient to upgrade the engine and set DBCENTURY in the engine when initializing, will depend on which side is doing the string-to-date conversion.

---

Generic SQL

• How can I do case-insensitive searches, e.g. search for “Smith”, “smith”, “SMITH”, or even “sMiTh” ?
  1. Break each word down to the individual letters, and search for the regular expression using both upper- and lower-case letters, e.g. to search for lname equal to “Smith”:

  SELECT * FROM customer
  WHERE lname MATCHES "[Ss][Mm][Ii][Tt][Hh]";

  The IIUG Software Repository, 4GL (noncaseqry) contains a 4GL function which will create this clause for you.

  2. Using an UPPER() function (see next question below), convert both the column and the filter value to uppercase, e.g.

  SELECT * FROM customer
  WHERE UPPER(lname) = "SMITH";

  3. Create a dummy column to store an all-uppercase copy of the value, e.g.

  ALTER TABLE customer ADD (uc_lname CHAR(20));
  UPDATE customer SET uc_lname = UPPER(lname);
  SELECT * FROM customer
  WHERE uc_lname = "SMITH";

If you are not using Illustra or Universal Server, this is not an easy thing to do. If the data is stored in mixed-case, your options are:

Option 3 is probably the best, because it is the only one which will make use of an index on lname. Option 1 will do a sequential scan (or possibly a scan of the entire index) to compare the value of lname to the regular expression. Option 2 will also do a sequential scan or scan of the index, perform UPPER() on each lname value, and compare the result to “SMITH”. These will be slow.If you are using Universal Server or Illustra, you can create a user-defined function, e.g. UPPER(), and create an index on UPPER(lname). This would allow you to store lname in mixed-case, and still use the index to search for “Smith”. A query such as:

SELECT * FROM customer
WHERE UPPER(lname) = "SMITH";

would use an index on UPPER(lname).

• How do I convert a character string to uppercase/lowercase?

Informix-SQL and 4GL have built-in functions UPSHIFT() and DOWNSHIFT() which convert a character string to uppercase/lowercase.

Version 7.3 engines now have built-in functions UPPER() and LOWER() to convert character strings to uppercase/lowercase.

For pre-7.3 engines, see the IIUG Software Repository, Misc (upper_spl, upshift_spl) for sample stored procedures to convert character strings to uppercase. These can easily be modified to convert strings to lowercase.

• Are there functional equivalents for Oracle’s DECODE, NVL, TO_DATE, TO_CHAR, etc. functions?

Version 7.3 engines provide these functions for Oracle compatibility. The functions work exactly as the Oracle functions do, except for the DATE functions, due to differences in the date types.

For pre-7.3 engines, check the IIUG Software Repository, SQL (orclproc).

• How can I find the location of a character within a string? Say I have the string “FOO-BAR”, how can I find the “-” in the string?

CREATE PROCEDURE Cut (string VARCHAR(255), delimiter CHAR(1))
  RETURNING VARCHAR(255);
  DEFINE i INTEGER;
  DEFINE loc INTEGER;
  DEFINE res VARCHAR(255);

  LET loc = FindStr(string, delimiter);
  IF loc = 0 THEN
    RETURN string;
  END IF;

  LET res = '';

  FOR i = 1 TO loc - 1
    LET res = res || string[1,1];
    LET string = string[2,255];
  END FOR;

  RETURN res;
END PROCEDURE;

CREATE PROCEDURE FindStr(str VARCHAR(255), ch CHAR(1))
  RETURNING INTEGER;
   DEFINE i INTEGER;

    FOR i = 1 TO length(str)
        IF str[1,1] = ch THEN
            RETURN i;
        END IF;
        LET str = str[2,255];
    END FOR;

    RETURN 0;

END PROCEDURE;

• I have a CHAR column that contains numeric data. When I do a query WHERE char_col = “14″, it comes back very fast, but when I query WHERE char_col = 14, it takes a really long time. I have an index on char_col. Why is it so slow?

When I upgraded from OnLine 4.x to 5.0 (or later), performance on queries WHERE char_col = 14 slowed to a crawl. Why?Contrary to what you might expect, a query “WHERE char_col = 14″ does not first convert 14 to the character string “14″ and then search for that. (I believe that version 4.x did that, and it was the fix to that bug - because it is a bug - that caused performance to slow down on upgrades from 4.x to anything else.) Instead, each value of char_col is converted from CHAR to INT, and then compared with 14. Why?

Remember that an index on a character column is sorted based on the character values, starting from the first character of the field. Therefore, in a char(5) column, the following values are sorted in this order: ” 14″, ” 21″, “0014″, “099″, “14″, “14.0″. Four of these values are equal to 14, and should be returned by a query WHERE column = 14. If, however, the value 14 was first converted to CHAR, resulting in the value “14″, only the fifth value above would be returned. Since the values equating to 14 can be spread throughout the index, the index cannot be used to find them. This query will use a sequential scan, or in the best case, an index scan, converting every value of char_col to INT before testing. For this reason, this use of implicit type conversion should be avoided. If the data is in character format, it should be compared with a character string, e.g. “14″.

• Why do I get an error when I try to SELECT NULL from a table?

NULL does not have a type, and therefore cannot be treated as a constant in a SELECT statement. The way to work around this is to create a stored procedure which returns a null of the datatype you want.

• I inserted the value 1.69999971 into a FLOAT column, but when I select the data, I get 1.7. Is this a bug?

Both FLOAT and SMALLFLOAT are very limited in the precision they support, which depends on how the specific computer internally stores floating point numbers. If the value contains more digits than the floating-point representation on the computer can support, the least-significant digits are treated as zeros. The erroneous value displayed is usually not actually an error in the display (although there have been bugs entered against ISQL and DB-Access for not displaying enough decimal places in the Query-Language option), but in the way it is stored. This is documented most thoroughly in the manual Informix Guide to SQL: Tutorial, version 7.1, p. 9-10, and also in the Informix Guide to SQL: Reference, version 7.1, p. 3-15.

• Why do I get an error when I add a month to certain dates?

The following SQL query demonstrates a problem in date/datetime arithmetic:

SELECT order_date + 1 UNITS MONTH FROM orders

This returns error -1267 “The result of a datetime computation is out of range” because one of the values of order_date is May 31, 1994. Adding a month to this would result in June 31, 1994, which is an invalid date. This is in accordance with ANSI standards.Couldn’t they have just returned June 30, 1994, you may ask. Yes, they could have, but you probably didn’t really want to do that anyway. What happens when you add a month to June 30, 1994? You get July 30, 1994, which is not the same as if you added two months to May 31, 1994.

What you probably want is a procedure based on business rules; for example, a procedure that finds the last day of a month.

---

OnLine

• How can I re-organize my table to consolidate my extents?
  How can I move my table from one dbspace to another?

ALTER FRAGMENT ON TABLE table1 INIT IN targetdbs;

• I have two users trying to update rows in the same table. Each user is going after a distinct set of rows: for example, user1 updates where customer_num = 10, user2 updates where customer_num = 20; there is no possibility that the users are attempting to update the same row at the same time. But they are getting locking errors. Is this Adjacent Key Locking? I have tried setting Isolation Level to Dirty Read, but it still doesn’t work.

First of all, it is important to realize that Dirty Read affects only queries, not updates and deletes. It is not possible to do a “Dirty Update”; that is, you cannot choose to ignore a locked row when doing an update. To do that, you would have to declare a simple cursor using Dirty Read, and then update individual rows based on the primary key, ignoring any lock errors, like so:

 WHENEVER ERROR CONTINUE  -- because the UPDATE will fail on a lock
 SET ISOLATION DIRTY READ
 DECLARE c1 CURSOR FOR SELECT unique-key WHERE { whatever your criteria are }
 FOREACH c1 INTO x
 UPDATE table WHERE unique-key = x
 END FOREACH

The only isolation level that has an impact on updates and deletes is Repeatable Read (more on this later); otherwise isolation level on updates and deletes is Committed Read.Next, think about how you are accessing the rows. Keep in mind that if the row is locked, the engine cannot read it, even just to determine whether it meets your criteria or not. Therefore, if any row which your session needs to read is locked, your query will fail with a lock error. The way to avoid a problem is to use an index to go directly to the row(s) you need. If your query uses a sequential scan, it will eventually fail if even one row in the table is locked, regardless of what that one row is. Also, be aware that even if your query uses an index, if your index cannot isolate the row you want, you will have to scan the rows returned by the index, which will fail if any of these rows are locked. It is very important to make sure that your queries use the best access path, which usually means an index which takes you directly to your row, and not requiring reading any other rows.

Ex.1. User1 updates rows where customer_num = 10. User2 attempts to update rows where customer_num = 20. Table has no indexes, therefore user2 does a sequential scan, and fails when he reaches customer_num 10, which is locked by user1. Although user1’s row contains customer_num 10, not 20, the engine cannot verify that the row does not meet the criteria, because it is locked. If there were an index on customer_num, user2 could use the index to go directly to customer_num 20, never having to read the row locked by user1.

Ex.2. User1 updates rows for city=”Menlo Park”, state=”CA”. User2 attempts to update rows for city=”San Francisco”, state=”CA”. State is indexed. User2 reads the index on state, which shows three rows with state=”CA”. User2 must then read the data rows pointed to by this index, and gets an error when he reaches the Menlo Park row locked by User1. If the index were on (state, city), user2 could go directly to the “San Francisco” row, and never try to read the row locked by user1.

So, how does Repeatable Read affect updates? The definition of Repeatable Read is that if the user were to re-run the same query/update/delete within that transaction, the same results would be returned. That is, data cannot be changed in any of the rows that were selected, nor can any more rows be added that would fit the criteria of the query/update/delete. For example, if I update all rows where customer_num = 10, then until I commit the transaction, not only can you not update any of those rows, but you cannot insert any new rows with customer_num = 10. This means that the adjacent index item must be locked to prevent you inserting this new row; this is very similar to Adjacent Key Locking (see the below-mentioned TechNotes article for more info).

For a full description of Adjacent Key Locking and Key Value Locking, see TechNotes 1994, Volume 4, Issue 3&4: B+ tree Item Locking in Informix-OnLine 5.x and Informix-OnLine Dynamic Server.

• If I declare a cursor using an isolation higher than dirty read, can I get it to skip locked rows? It seems like when I hit a lock and get an error, the next FETCH will return the same error.

When you get a lock error on a FETCH, your next FETCH will re-attempt to fetch that same row, hoping that the row has been released. There is no way to change this behavior.

What you can do is use two cursors, with different isolation levels, to skip locked rows. Here’s a 4GL code fragment I wrote which will do that:

 DEFINE c_num INT
 DEFINE c_rec RECORD LIKE customer.*

 WHENEVER ERROR CONTINUE
 SET ISOLATION TO DIRTY READ

 DECLARE c1 CURSOR FOR SELECT customer_num FROM customer
 FOREACH c1 INTO c_num

   SET ISOLATION TO COMMITTED READ

   DECLARE c2 CURSOR FOR SELECT * FROM customer WHERE customer_num = c_num
   OPEN c2
   FETCH c2 INTO c_rec.*

   IF sqlca.sqlcode = 0 THEN
     DISPLAY c_rec.customer_num
   ELSE
     DISPLAY "error ", sqlca.sqlcode, ": skipping to next record"
   END IF

   SET ISOLATION TO DIRTY READ
 END FOREACH

• When I try to initialize my OnLine engine, I get:

08:50:57 mt_shm_init: can't create virtual segment
08:50:57 shmat: [EINVAL][22]: shared memory base address illegal
08:50:57 using 0x80600000, needs 0x080800000

If I change the SHMBASE to the needed value, I get the same message, only with different values. What’s wrong?This error message is usually returned when some kernel parameter (usually SHMMAX) is not set high enough either to allocate the needed shared memory in a single shared memory segment, or to allocate the memory at all (usually the single segment). Try increasing SHMMAX and re-build your kernel.

• What is the maximum size of a chunk? What is the maximum offset+size for a chunk?

The theoretical maximum size of a chunk is 1,048,576 pages, regardless of whether you are on a 64-bit OS. This is because of the page numbering convention used by OnLine: the page address is represented in hex as CCCPPPPP — 3 hex digits for chunk number, 5 hex digits for logical page number in the chunk. So the maximum page number in a chunk is 0xFFFFF, or 1048575 (since the first page number is 0, this equates to a maximum of 1048576 pages). This means that if your port uses a 2KB pagesize, the maximum size of a chunk is 2GB. If your port uses a 4KB pagesize, the maximum size of a chunk is 4GB. On some OS’s, lseek may fail if a value over 2GB is passed to it; this will limit your chunk size to 2GB. For this reason also, the offset+size of the chunk may be limited to 2GB, meaning that you cannot create a 2GB chunk with a .5GB offset, or any other combination that would add up to more than 2GB.

The maximum supported chunk size is 2 GB. This is documented in the Informix-OnLine Dynamic Server Administrator’s Guide. For Version 7.1, it is on page 14-5.

• I deleted a lot of rows from my table. Why is it still taking up so much space?

When your table grows, new extents are allocated to that table from the free space in the dbspace. When rows are deleted, these pages are not released. Even if all the pages in an extent are empty, the extent remains allocated to the table. The only way to reclaim the extent is to re-build the table, possibly by using ALTER TABLE or by exporting it.

• How can I use/save an HTML page greater than 32k using the Web DataBlade? I am getting the error “Results exceeded maxsize (32768)”.

Increase (or set) MI_WEBMAXHTMLSIZE in your web.cnf file.

The Webdriver allocates 8k of memory for storing the results of a app-page request. It then dynamically allocates more memory as needed, up to the value of MI_WEBMAXHTMLSIZE.

MI_WEBMAXHTMLSIZE defaults to 32K and can be modified by adding it to the web.cnf file. The syntax is:

MI_WEBMAXHTMLSIZE n

where n is the maximum number of BYTES that you want to allocate to a Webdriver thread.

• How can I limit the number of rows returned to the browser by the Web DataBlade?

In some versions of Web DataBlade, there is a MAXROWS attribute for the MISQL tag to limit the number of rows returned. This is documented in the Informix Web DataBlade User’s Guide. In later versions, this has been superceded by WINSIZE. Check the release notes for the Web DataBlade.

---

Archiving

• Does ontape support archiving (or logical log backup) to disk?

Not only is this question frequently asked, it is frequently answered incorrectly.

YES - ontape supports disk files. If you need documentation, see page 12-6 in the INFORMIX-OnLine Dynamic Server, Archive and Backup Guide, Version 7.1:

A user with one tape device might want to redirect a logical-log backup to disk file while the tape device is in use for an archive.

This has been issued as Tech Alert 6125. Tell this to any Informix person who tells you this is not supported.

• What is the maximum tape size that can be used with ontape?

The maximum TAPESIZE value is 2147483647 (2 Gig), but TAPESIZE is specified in Kbytes, making the maximum tape size 2 TB (Terabytes).

Some platforms limit to 2GB the amount that can be written ( using write() ) on a single open(). Since ontape uses open() and write(), this will limit the tape size to 2GB; this is an OS, not Informix, limitation.

If you are using a Unix file for your tape device, however, you may be limited to 2GB. This is bug 76256, which is fixed in 7.24 and later versions.

• Can I take an archive from OnLine version x and restore it on OnLine version y?

No. It is only supported to restore an archive taken with the same version of OnLine.

• Can I use onunload to unload a table from OnLine version x and onload it on OnLine version y?

No. Onload only works on onunload files taken from the same version of OnLine. See the Informix-OnLine Dynamic Server Administrator’s Guide, in the chapter OnLine Utilities, for onload/onunload:

Constraints That Affect onload and onunload
The original database and the target database must be from the same release of OnLine. You cannot use onunload/onload to move data from one release level to another.

• Can I use OnBar’s point-in-time recovery to recover a dbspace up to a specific time?

No. Although OnBar can be used to restore a specific dbspace, and also provides point-in-time recovery, they cannot be used together to bring a dbspace up to a point-in-time earlier than the rest of the system. A warm restore of a dbspace must include a logical recovery which brings the dbspace in sync with the rest of the instance.

---

ESQL/C

• Can I compile ESQL/C code for use with C++ ?
  • You break out the ESQL/C code from the C++ code; they must be in separate modules.
  • You do not use structs containing function members in the code that ESQL/C is aware of: no member functions or classes.
  • You do not use C++ comments in the code processed by ESQL/C.

There is no supported way to use ESQL/C with C++. However, it is possible to combine ESQL/C code with C++ if:

See the IIUG Software Repository, ESQL/C (esql_c++) for detailed directions.

• 0 Comments

---

The original of this article appears at The Esperanto Group. Some slides from “introducing Informix as a general database platform. In HTML format.” are there as well.

Informix Universal Server

Kerry Sainsbury (kerry@kcbbs.gen.nz), 8 March 1999

Ever since Informix “released” Universal Server at the end of 1997, I’ve been keen to get my hands on it. Now that a trial edition has been made available at www.intraware.com, I finally got the opportunity to try it out. This document describes some of the very cool features of “Universal Server” that distinguish it from the regular “Dynamic Server”, yet doesn’t shirk the important task of highlighting some of the things that really *suck* about the product.

This document also works as a tutorial, so if you want to download the product and fire-up dbaccess, feel free!

Row Types

Informix Universal Server (IUS) gives us the concept of ‘row types’. These are approximately the equivalent of ‘classes’ in an object-oriented environment, just without any methods:

create row type person_t ( name varchar(30), birthdate date );

‘row types’ support inheritance. Here’s the definition of a cartoon-character class, which inherits all the properties of the ‘person_t’ type, and adds a ‘popularity’ indicator in the form of an integer:

create row type character_t ( popularity int ) under person_t; 

These ‘row types’ are, like classes, just definitions however. In order to actually store some instances of the classes, we need to create a database table in which to put them. The following table, called ‘character’, stores rows of the ‘character_t’ class:

create table character of type character_t;

Inserting data into this table is done the same way as a regular SQL’92 table:

insert into character ( name, birthdate, popularity )
 values ("Mickey", "02/02/1946", 10 );
insert into character ( name, birthdate, popularity )
 values ("Donald", "03/03/1953", 9 );

Classes (ie: ‘row types’) can include other classes within them. That is, they support both inheritance (’is-a’) and composition (’has-a’) models. The follow example first creates an ‘address’ class, then an ‘employee’ class which includes an address, and finally an employee table to store the employee objects.

create row type address_t ( description varchar(30),
                            city varchar(30) );
create row type employee_t ( startdate date, salary int,
                             address address_t ) under person_t;

create table employee of type employee_t; 

Now when we want to insert a row into the ‘employee’ table, things start getting a little more interesting. The insertion of the ‘address’ portion of the ‘employee’ needs to be explicitly labelled with the ‘row(….)::address_t’ notation:

insert into employee ( name, birthdate, startdate, salary, address )
values ("Joe Bloggs", "7/9/1963", "7/10/1984", 90000,
row("101 Toontown Sq, Disneyland", "Anaheim CA")::address_t );

At this point we encounter the first limitation of IUS — I’m damned if I can figure out how one can insert a row with partial row type information for the address. I seem unable to ignore the ‘city’ portion of the address, for example. You have to specify the full ‘address’ information.

Let’s move on to some cool stuff now, and worry about the implications of the above limitation later.

Next up we create a new sub-class of ‘employee’, especially for managers. Then we create a ‘management’ table, in which to place our ‘management’ objects. The key thing to notice is that we will create the new table ’under’ the employee table - previously we had only created new ‘row types’ under other ‘row types’.

create row type management_t ( stresslevel integer )
  under employee_t;
create table management of type management_t
  under employee; 

Now let’s put a row into the ‘management’ table - that ‘row(…)::address_t’ notation’s still there, remember.

insert into management (stresslevel, name, birthdate,
startdate, salary, address )

values (99, "Walt Disney", "7/9/32", "7/10/45", 12500000,

  row("Rich Dude Hills", "Beverly Hills CA")::address_t ); 

And let’s do a simple SELECT, to see what we’ve got in management.

select * from management; 

Just the ‘Walt Disney’ row is returned, but what if we SELECT from ‘employee’:

select * from employee; 

Cool - Table inheritance! Not only do we see our ‘Joe Bloggs’ row, we also see the ‘Walt Disney’ row we inserted into the ‘management’ table. Very excellent Informix! And just to prove that we can extract just the ‘employee’ data if we need to, we can use the following syntax:

select * from only(employee); { Now just get employees }

One issue with these ‘row types’ is that you need to have a development tool that understands how to interpret these special data types. If you don’t, you’ll need to select the nested row type information columns individually, eg:

select name, birthdate, startdate, salary,
       address.description,
       address.city from employee; 

Updating tables is fairly straight-forward. The only ‘tricky’ part is updating the ‘nested row type’ columns, like address. The following example fixes the address’s city information in the employee table:

update employee
   set address.city = "Auckland"
 where address.city = "Oakland" 

In order to update the entire ‘address_t’ information, you’ll need to use the same ‘row(…::address_t) notation we used in the ‘insert’:

update employee
   set address = row(address.description,
                    'Auckland')::address_t
 where address.city = "Oakland" 

Tables don’t have to be created ‘of type x_t’. You can create ‘regular’ database tables which just use ‘row types’ as column definitions:

create table ad_agencies( name varchar(30),
address address_t, contact person_t ); 

insert into ad_agencies values
   ("Saachi and Saachi",
row("321 Expensive Location", "City")::address_t,
row("Mrs Shipley", "11/04/1942")::person_t ); 

Stuff that sucks

There is no ‘alter row type’ command, in fact, if you have a ‘typed table’ (like the ‘employee’ table which was created ‘…of type employee_t’), the only column adjustment ‘alter table’ can do is ‘drop type’.

‘drop type’ retains all the data in the table, but replaces the ‘row type’ structures with regular database columns. Our ‘employee’ table would change from (startdate date, salary int, address address_t) to (startdate date, salary int, description varchar(30), city varchar(30));

The reason you might want an ‘alter row type’ is if, for example, you decide it would be a good idea to alter ‘person_t’ to include the person’s shoe size. Not unreasonable, huh?

The bad news is that you can’t change the definition of ‘person_t’ without quite a bit of work. You’ll have to alter every table which features ‘person_t’ so that it doesn’t include ‘person_t’, insert a column to hold the new data, and recreate the row-type. For simple tables like ‘character’, this isn’t too difficult:

alter table character drop type;
alter table character add( shoesize integer before popularity ); 

…but then we get to employee and management (and their associated row types)

“alter table employee drop type” doesn’t work, presumably because the ‘management’ table is ‘under’ it. “alter table management drop type” doesn’t work, presumably because it’s ‘under’ employee.

In order to make this work, you would have to:

• Select the data from every table which uses the ‘person_t’ row type, and put it into a temporary tables.
• Alter the temporary tables, placing a new ’shoe_size’ column after the ‘birth_date’ column in each of the tables.
• Drop all the original tables.
• Drop all the person_t row type descendants
• Drop the ‘person_t’ row type.
• Recreate the original tables, using the structure of the new temporary tables.
• Create the new ‘person_t’ row type.
• Recreate all the person_t row type descendants.
• Alter the tables, addings ‘person_t’ row types (and descendants) as appropriate.
  

Just absurd! Even if somehow you managed to keep all the above items straight, I’m sure the referential integrity contraints would drive you nuts anyway!

I’m not sure it would work anyway. What happens if you have a non-typed table, like the ‘ad_agencies’ table, and you attempt to remove the ‘employee_t’ type - you’re going to end up with two ‘name’ columns (one for the name of the agency, and another for the name of the employee). Yuck!

Of course none of this matters if you create databases which never need to be changed :-)

DISTINCT DATA TYPES

IUS adds what it refers to as ‘distinct’ data types. They are an approximation of SQL92 ‘domains’, which act as aliases to built-in datatypes:

create distinct type phone_number_t as char(7);
create table informant(name char(10), phone_number phone_number_t);

This is fine, but doesn’t really go far enough. You can’t specify ‘not null’. You can’t specify a default value. Most importantly, there is no ‘alter distinct type’, so if you decide that phone numbers need to be longer, you’re simply out of luck.

Somewhat assumingly, there is a ‘drop type’ command, which is quite happy to execute, even if there are tables which use the type! Recreating the dropped type won’t let you recover your table either :-(

drop type phone_number_t restrict; 

select * from informant; // bang!!

OPAQUE DATA TYPES

…are custom data types, which the engine knows nothing about. You define:

• A data structure for how the data is stored on disk
• Support functions to determine how to convert between the disk format and the user format
• Secondary access methods that determine how the index on this data type is built, used, and manipulated
• User functions that use the data type
• A row in a system catalog table to register the opaque type in the database
• Methods to control use of operators, aggregates, and casting.

This is all defined via a C-based API. I did not investigate this facility - I couldn’t see myself ever needing it!

SETS

IUS has some simple support for ’sets’, eg:

create table item( item_number integer,
                   description varchar(40),
                   colours set (char(10) not null )); 

insert into item values ( 1234, 'gardening clogs',
                   "set{'red','blue','green'}" ); 

insert into item values ( 5678, 'pruning hosery',
                   "set{'yellow'}" );

What we’ve defined here the fact that the ‘item’ table contains a set called ‘colours’. We then inserted some values into the table. The big thing that’s missing, is that we don’t have the ability to specify what the valid values for the set are — we can insert ‘Tuesday’ into our set of colours, and nothing will complain! There should be (but isn’t) a syntax something like the following:

create set colours_t char(10) of
{'red', 'blue', 'green', 'yellow', 'magenta'}

which would then support:

create table item( item_number integer,
                   description varchar(40),
                   colours colours_t not null);

Enough of my fantasys however. The ’set’ support that does exist lets us do things like list the items which are available in red:

select * from item where 'red' in colours;

… and list the items which have more than one colour available:

select * from item where cardinality ( colours ) > 1;

There are some variations on sets - ‘multisets’ allow a value to be repeated in the set, and ‘lists’ are ordered sets, eg:

create table city_temps( city_name char(30),
                         avg_temps LIST(integer not null));

What’s naff about sets?

When you update a collection type, you must update the entire collection! You can’t update individual items in the collection. Doh!

I can’t see how I can access the elements of the various ’set’ types. There was no syntax I could find that would tell me what colours a given ‘item’ was available in.

I was expecting, at least, some syntax which would return a pseudo-join result set, like this:

item_number description      colours
1234        gardening clogs  red
1234        gardening clogs  blue
1234        gardening clogs  green
5678        pruning hosery   yellow

I was unable to find any syntax to access the ‘ordered’ information in a ‘list’ set. How can I iterate through the list? Find the first, or last element in the list? I figure I must be missing something!

STORED PROCEDURES

IUS stored procedures support method overloading. We’re finally back at something cool! Let’s create a procedure which calculates a 1% bonus for employees.

create procedure bonus( emp employee_t ) returning money;
   return emp.salary * 0.01;
end procedure;

If we run the procedure, we see that both regular employees (Joe Bloggs) and management (Walt Disney) get the same bonus:

select name, salary, bonus(employee) from employee 

Now let’s create another ‘bonus’ procedure, but this once takes a ‘management_t’ row type as an argument, instead of the ‘employee_t’ the previous one did. Notice that we do not have to drop the existing ‘bonus’ procedure:

create procedure bonus( emp management_t ) returning money;
   return emp.salary * 0.5;
end procedure;

Running the same SELECT statement now gives the regular employees a 1% bonus, while management get a 50% bonus:

select name, salary, bonus(employee) from employee

While this is very nice stuff, I have to wonder why the procedures have been created seperately from the row-type information. One of the big things about the OO paradigm is the way data and procedures are drawn together, so I would’ve expected the syntax to be more like:

select name, salary, employee.bonus() from employee

Perhaps it’s a SQL3-ism.

OBJECT IDs - ‘ref’ and ‘deref’

I read Michael Stonebraker’s “Object-Relational DBMSs - Tracking the next great wave’ book when creating this document. In his book he talks about the ability to include a reference to another object in a table, eg:

create row type department_t( name string,
manager ref(management_t) references management,
workers set(ref(employee_t) references employees ) ); 

The above example defines a department, which directly references a row in the management table, and a set of rows in the employee table.

This techique would move much of the need for joins, and be generally rather sexy. In fact Stonebraker says that in order for row types to be useful, an object-relational db must include these ‘ref’ and ‘deref’ functions.

So guess what’s not in IUS?

Summary

The release I downloaded (9.14.TC1 for Windows NT, 17 Apr 1998) has some very interesting technology underneath it, but I was disappointed at its lack of suitability for any ‘real-world’ database - meaning, one in which the schema may need to be altered!

Finally, I need to credit Mary Mudie’s “Informix-Universal Server” chapter in “Informix Unleashed”, which helped provide some of the examples I’ve used in this document.

Kerry Sainsbury

kerry@kcbbs.gen.nz

• 0 Comments

Understanding and Using Informix Stored Procedures

Rafal Czerniawski

Introduction

Since the introduction of stored procedures in version five of the Informix engine there has been a lot of discussion about their use, benefits, limitations and their impact on the applications’ performance. They have gained acceptance in the client/server environment but their usefulness in the single host type environment has been seriously underestimated. As with any tools, before using them it is important to consider whether it is the right tool for the job at hand. To be in the position to make this decision you need to understand its workings, how well it integrates with your environment and development tools, the type of application you are developing and whether it is a small scale application or large mission critical OLTP system. When used appropriately stored procedures can be a very powerful tool both in the client/server environment and single host type environment.

The aim of this article is to provide a high level overview of stored procedures and their features, particularly the advantages and disadvantages of their use from the two perspectives: client/server and single host type environment. I will also discuss issues to consider before using stored procedures on a large scale. Hopefully by the end of this article you will be in the position to decide whether stored procedures are the right for you.

Overview of Stored Procedures

Stored procedures can be thought of as user defined program modules stored in the database and executed by the database engine. They are the simplest form of remote procedure calls where the remote client sends a message to the server, which then executes the procedure. Stored procedures are written in Stored Procedure Language which is a superset of SQL with additional looping and conditional statements. When a stored procedure is created it’s source and the compiled executable is stored in the system catalog tables. From the administration point of view they are like any other database objects which can be created and dropped using ‘create’ and ‘drop’ statements. Their permissions can be administered using grant statements. The source of stored procedures can be extracted from the system catalogs using ‘dbschema -f’ utility. To fully appreciate the benefits and shortcomings of stored procedures it is important to understand what happens at the time stored procedure is created and executed.

Stored procedures are created using a ‘create procedure’ statement. The following steps take place at the time of creation:

1. The source is parsed and stored in the SYSPROCBODY system table.

2. The SQL is extracted from the source of stored procedure and a dependency list built. The dependency list is an index to all database objects accessed by the stored procedure. It is used by Informix at the time of execution to verify that all objects referenced still exist and have not changed.

3. The extracted SQL is optimised to produce a query plan, just like any other SQL.

4. The dependency list and query plan are stored in the system table: SYSPROCPLAN. Note that while the stored procedure is created Informix places exclusive locks on some of the system tables.

Stored procedures are executed using ‘execute procedure’ statement. Following steps take place during execution:

1. Retrieve the dependency list, pcode and query plan for the stored procedure from the system catalog tables if they are not already in the stored procedure cache.

2. Verify that the objects in the dependency list have not changed since the time the stored procedure was created.

3. If no changes were detected execute procedure.

4. If there were changes to the database object, the SQL is re-optimised and a new query access plan is stored in the catalog tables. The procedure is then executed.

The SQL in the stored procedures is optimised at the time of its creation except in the following cases:

1. When the structure of the tables referenced by the procedure or their indices have changed it will be re-optimised at the first execution after the change.

2. If stored procedure uses temporary tables it will be re-optimised every time it is executed. This has been fixed in later versions of 5.x

3. If the stored procedure does not contain any DML statements (e.g. select, insert, update, delete) it will be re-optimised every time it is executed. This has been fixed in later versions of 5.x. The re-optimisation of SQL during stored procedure execution should be avoided as it results in exclusive locks on the SYSPROCBODY table and can lead to serious concurrency problems.

Stored Procedures System Tables

The following system tables are used to store information about stored procedures:

SYSPROCEDURES - General information
SYSPROCBODY - Pcode executable
SYSPROCPLAN - Dependency list and query plans
SYSPROCAUTH - Security authorisation

Security and Stored Procedures

Stored procedures can operate in two security modes at the time of execution:

1. Owner Privileged Procedures

These are stored procedures created by the user with ‘resource’ authority on the database. The stored procedure inherits all the permissions on the objects owned by the creator plus permissions on other objects that been granted to the creator with the grant option. In case of owner privileged stored procedures Informix will check all permissions on referenced objects at the time of execution.

2. DBA Privileged Procedures

These are stored procedures created by the user with ‘dba’ authority on the database or with a dba keyword in the create statement. In this case the stored procedure has ‘dba’ privileges for the duration of it’s execution. When dba privileged procedure is executed there is no need to check the permissions on referenced objects.

Benefits of Using Stored Procedures

For the purpose of this article it is useful to look at the benefits of using procedures from the perspective of the environment you will use to deploy your application. That is whether it is client/server or a single host type environment. Note that the benefits gained from using stored procedures in a single host type environment are also applicable to client/server.

Advantages in Client/Server Environment

1. Provide means for partitioning the application logic between the server and client. For example CPU intensive work such as calculations could be implemented in the stored procedures and the results returned back to the client for presentation.

2. Improve application performance by reducing network traffic. One of the biggest performance bottlenecks in the client /server environment is the network. With use of stored procedures you can substantially reduce network traffic. There are two reasons for this:

a. Instead of sending whole SQL statements only the stored procedure name and arguments have to be sent to the server.

b. In a situation where you have a transaction which consists of multiple SQL statements the intermediate results from the SQL have to be sent between the server and the client. If stored procedures are used then all transaction work can be done on the server and only the final result returned to the client.

3. Allow encapsulation and enforcement of business rules on the server. For example if business rules are controlled through stored procedures then they can be enforced regardless of what tool is used to update the database.

4. Improved application maintenance. If business rules and application logic are implemented in the stored procedures then any changes to business rules are localised. This means that application changes can be done on the server without a need to distribute them to all the clients.

5. Provide more robust transaction control. When using stored procedures transaction control can be implemented on the server instead of the client. If transactions are controlled on the client there exists a possibility of transactions being left open if the client crashes. Open transactions might prevent archiving of logical logs and lead to long transactions. This problem has been addressed in later versions of release 5 where Informix will terminate any open sessions without client connection.

6. Allow to extend the functionality of some 4GL development tools. Some of the Client/Server development tools are not very good in handling complex database interactions such as denormalised data.

7. Improve application security. In ODBC environments any application users can access the database using desktop tools. Using stored procedures you can eliminate the need to grant direct access to all tables and control it through the procedures.

8. Can be used to invoke Unix programs from the PC client on the server using SPL ‘SYSTEM’ command. For example a stored procedure can be used to send Unix mail or update files on the Unix host.

Advantages in Single Host Environment

Note that all the benefits of stored procedures in the single host environment are also applicable to client/server environment

1. Allow implementation of business rules and database integrity checking in the database by disallowing programs and users from accessing the tables directly.

2. Encourage code reuse. Commonly used functionality can be implemented in the stored procedures which can be reused by any programs.

3. Improve performance. When SQL is executed through 4GL or E/SQL, the SQL statement is sent through a pipe to the Informix engine and then parsed, optimised and security checked before it’s execution. With stored procedures the above work needs to done only at the time of it’s creation. Generally stored procedures with four or more SQL statements provide best improvements.

4. Improve application security. If database access is encapsulated within the procedures then it is no longer necessary to grant privileges to users on the tables.

5. Allow to extend the functionality provided by standard SQL functions. Stored procedures can be used to create user defined functions that are transparently utilised within the SQL statements, for example: if a measurement is stored on the database in inches. A function could be written which will convert inches to centimetres. This function can be then used as if it was built-in SQL function.

6. Extend functionality provided by the database triggers. The language used to write triggers is limited to simple type operation.

7. Hide the complexity of the underlying database structures from the users and programmers, for example stored procedures can be written to perform standard reports which are then invoked by GUI end-user reporting tools.

8. Aid in application’s performance tuning and monitoring. Procedures provide ability to perform SET EXPLAIN on SQL with out first executing it. Since the stored procedure’s SQL is optimised when the procedure is created, it is possible to obtain a