Microsoft Windows PowerShell and SQL Server 2005 SMO – Part I
20 Sep
Posted by ProCOM 
on September 20, 2007 – 5:33 pm - 1,725 views
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 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
Using a Correlated Subquery in a T-SQL Statement
20 Sep
Posted by ProCOM on September 20, 2007 – 11:47 am - 1,211 views
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 <l; (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
Very Useful Programing Articles Digest
13 Aug
Posted by ProCOM on August 13, 2007 – 11:24 pm - 614 views
- 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
del.icio.us Cool Stuff On Web Developement
04 Aug
Posted by ProCOM on August 4, 2007 – 12:50 am - 1,382 views
- 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
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
- What documentation does Informix have on Year 2000 compliance?
This varies by Operating System.
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” ?
- 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”:
- Using an UPPER() function (see next question below), convert both the column and the filter value to uppercase, e.g.
- Create a dummy column to store an all-uppercase copy of the value, e.g.
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.
SELECT * FROM customer WHERE UPPER(lname) = "SMITH";
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).
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.
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).
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;
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″.
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.
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.
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;
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.
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
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.
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.
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.
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.
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?
- What is the maximum tape size that can be used with ontape?
- Can I take an archive from OnLine version x and restore it on OnLine version y?
- Can I use onunload to unload a table from OnLine version x and onload it on OnLine version y?
- Can I use OnBar’s point-in-time recovery to recover a dbspace up to a specific time?
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.
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.
No. It is only supported to restore an archive taken with the same version of OnLine.
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.
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.
Informix Universal Server – Kerry’s Perspective
22 Jul
Posted by ProCOM on July 22, 2007 – 2:10 pm - 440 views
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
Understanding and Using Informix Stored Procedures
22 Jul
Posted by ProCOM on July 22, 2007 – 2:06 pm - 2,237 views
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 access paths chosen by the optimiser at this time. In the case of SQL executed from E/SQL or 4GL it is not possible to see the explain output unless the SQL has been run against the database which might not be always possible or practical in a production environment.
9. Can be used increase application’s independence from the underlying database structures, therefore, isolating the programs from the database structure changes, for example: data denormalisation.
Disadvantages of Stored Procedures
Most of the disadvantages of stored procedures are due to an extra layer of complexity and lack of support by development and administration tools
1. Some of the 4GL development tools are designed to work best by accessing the database tables directly. If you are using stored procedures then some of the default functionality provided by the development tool might be lost. For example most of 4GL tools will automatically implement optimistic locking (checking for lost updates). If you choose to perform updates through stored procedures then in most cases you will lose the functionality provided by the tool which will result in decreased productivity.
2. For the above reason, when using stored procedures you might lose the database independence provided by your 4GL tools. Also the stored procedure’s code is not portable to other DBMS platforms.
3. More complicated program debugging.
4. Before version seven of the engine, stored procedures are buffered in the sqlturbo processes hence each user holds their own copy of the stored procedures cache. As a result of this your application might require more memory for each sqlturbo process and you will lose the benefits of sharing buffered procedures across different database sessions. This is no longer a problem since version seven has a common stored procedure cache for all the users.
5. Extra dependency is introduced into the applications. It necessary to ensure that the programs execute correct versions of the procedures. Configuration management is a major issue. 6.In some situations the use of stored procedures might degrade the performance. As stated earlier, when stored procedure is executed the Informix engine has to check the system tables to ensure that the tables used by the procedure have not changed.
7. The stored procedure language does not support dynamic SQL and versions prior to seven did not support access to SQLCA.
Using Stored Procedures
As illustrated, many benefits can be gained from using stored procedures. When embarking on their use, consider the impact on the developers due to the extra layer of complexity in the environment. It is best to carefully plan how they will be utilised and administered. Following is an overview of issues that should be considered before using stored procedures:
1. Devise naming standards. Good naming standards are important with a large number of stored procedures. They will improve the maintainability of the application by allowing easy identification of stored procedure function and the tables it accesses.
2. Document SPL coding standards. The standards should address issues such as:
a. Guidelines on when to use stored procedures. You might decide that only certain type of work should be done in stored procedures. Good candidates are: complex reports and queries, access to sensitive data, processing of large cursors, any SQL that is used repetitively, transactions which perform more then four SQL statements.
b. Document conventions for parameter passing between the application programs and stored procedures.
c. Define how the stored procedures will communicate back with the application.
d. Provide guidelines for transaction control. Transaction control can be either done from the application program or within the stored procedure. The choice will largely depend on the development tools at use. Try to stick to one consistent method.
3. Error handling. How will the errors handled and communicated back to the application programs.
4. Workout how the dependencies between the procedures and programs will be tracked.
5. Update configuration management procedures.
6. Management of SYSPROCBODY and SYSPROCPLAN tables. When a large number of stored procedures is used these tables can grow to many extents which can effect the performance of loading the procedures into memory. This can be eliminated by altering the ‘next extent’ to a larger size.
7. Security administration.
8. Remember that by default stored procedures are created with execute to public permission. When migrating stored procedures to production execute to public should be revoked.
Summary and Conclusion
As you can see stored procedures are very powerful tools when used appropriately. The decision to use stored procedures should depend on your organisation, development environment and the type of application being developed. All of the benefits of store procedures can be fully realised when building large mission critical applications, where database integrity, security and performance with a large number of concurrent users are critical. Stored procedures should be also given serious consideration when building client/server applications which will be deployed over the wide area networks where the amount of network traffic will have a significant impact on your application’s performance. In most cases you will find yourself using stored procedures just for a specific tasks which cannot be easily done using your current development tools or in situations where central enforcement of business rules and security is critical to the application.
Rafal Czerniawski is director of Dataspace Consulting Pty Ltd. He specialises in logical and physical database design, performance tuning, database administration, enterprise client/server design and CASE tools, his experience includes Informix, Sybase and DB2. You can contact him through email at: rafal@dataspace.com.au.
Tips and Tricks on OO GUI Development Using Informix NEW ERA
22 Jul
Posted by ProCOM on July 22, 2007 – 1:56 pm - 421 views
Tips and Tricks on OO GUI Development Using Informix NEW ERA
By Don Udawattage (email: taraka@alphalink.com.au, udadon@hotmail.com)
Having worked as a major developer on a large commercial application developed in Melbourne using the full power of New Era and having many years prior experience of OO GUI Client Server Database application development, I am in a position to identify the strength of this powerful truly OO product, despite its not being well known in Australia. New Era has some inherent problems which stand on the way of serious development and we have found sound solutions to overcome them and made robust applications. For some of you, these findings may look like reinventing the wheel because you may have come across the same problems and found better solutions long time ago. If what I am talking about in this note seems like gibberish and does not make sense to you(not an unreal possibility), please do not hesitate to Email or Phone me. The most up to date copy of this note may be seen in my homepage in the internet(URL: www.alphalink.com.au/~taraka). My home number is +61 3 9395 2637 and my mobile no is +61 0402 044 652 – I am an Australian. If you are viewing this document on a browser, it is convenient get a print and read. At the end of this paper, there is a hypertext link to download software mentioned in this paper.
Part A: To Make your Application Maintainable and User Friendly Focus on the Following
A1. Create Your own Base Classes of Visual Objects
Create your own base classes of visual objects(windows, list boxes, text boxes, superfields etc.) derived from New era base classes by simply cloning the 4GLs & 4GHs so that you can add your own common member functions and variables. In this way generic changes, found necessary later, could be implemented easily. When you paste a visual object such as a text box on a window, change its class name property from ixTextbox(for example) to your site’s textbox class. If you develop subsystems, create a separate window base class for each subsystem so that, for example, if your manager wants on the eve of implementation, the windows of different subsystems to have different background colours, you can do this easily, making him happy.
A2. Implement a Naming Standard
Have a standard for naming visual objects and classes and variables, so that they can be recognisable in 4gl code. For example, you may decide all your list boxes should end with lowercase abbreviation, preceded by underscore(e.g _lb). In addition to being readable, you can write generic functions which, by looking at the name can create, references to them using New Era’s powerful function ixWindow. GetContainedObjectByName() which we have found usefull to build a vector of all the controls in a window at runtime – I will explain later why we had to do this. If your visual object receives data from a column of a database, use the column name as a part of the object name. If the name of the column in the database table is state_code then the list box on your window representing this column can be named state_code_lb. Also name your labels so that the name of the label can be derived from the name of the object. The label of the state_code_lb listbox can be named state_code_la, replacing lb with la. This will help to add the label to error messages in generic functions used to validate data entered into visual objects.
Reward of Naming Standard
Towards the end of the project we found that some of our users type into data base key fields, illegal characters(such as asterisks) which cause problems. Are we to revisit all the programs to put in validations to prevent this and ask the users to retest ?Where is the time and money ?. Because all our key columns end with _code(e.g. state_code) and our visual objects(eg. Text Boxes) reflects this name, we could simply override the keypress event of the base class to not to accept offensive characters for key fields. However, we could solve this problem more elegantly in the SQL class covered later – a stitch in time saves nine !
A3. Make List Boxes Intelligent
A New Era list box provides basic functions to get/set the whole text on a line but for serious database applications, this is not enough. Provide functions to load data from the database by passing the table and column names and the list box will show the descriptions and the foreign key associated with that description is stored in a vector & when you want to save to database, your functions returns it. You can introduce members to indicate whether a list box is mandatory so that blank entries can be inserted for non-mandatory list boxes automatically. These are only a few of many features.
A4. Split your Big Applications into a Number of DLLs
Do not create a single application unless it is small. Group windows belonging to a sub systems into separate dll’s and have a small main executable to drive the dll’s. Windows dll’s are loaded only when functions in them are referenced(really not true in the 16 bit New Era version we used – I hope this will be fixed). Maintain them in a common network drive server and copy them to user’s C: drive when he logs in if the version in his C: drive is older than that of the server. You can do this by writing a program which runs before showing the login/password dialog box(I have written such a program to do this on my home PC, using Visual Basic, Email me for a free copy). This way, loading dll’s will be faster as they are loaded from your C: drive and you have full control of release of dll’s in the server and update them easily knowing very well that when users logs in next time, the latest version is available.
You must create dll’s for your own visual or non visual classes which your developers are obliged to used so that they do not have access to the source code.
A5. Provide Functionality to Hide All Windows at Once
Imagine your user has opened 5 windows in your New Era Application and suddenly wants to open up XL without having to exist the application so that once he finishes the XL session, he can resume his work.Your user’s 5 windows have filled the screen. There are so many ways to do this but the best way is to provide a preferences drop down menu in each window and this menu contains an item called “Hide” so that when the user clicks on it, all the windows will be hidden and a small but prominent window appears on the left top corner of the screen giving the entire space to the user. When the user wants to return to the application, he only has to click on this small window and all 5 windows will pop up restored to its original state instantaneously.
You can do this elegantly by creating a preference drop down menu in the base class window along with a function called within the constructor extension so that every time a new window is created, this function adds the window reference to a global array(i.e. a vector, in a global dll). Then put code in the Hide menu item in the Preferences drop down menu to navigate the window array and call hide event of each window then open up the small prominent window, containing a single button whose activate event navigates the window array and call the show event. All the windows developed by programmers, derived from your base class window, will inherit the preferences drop down menu along with all the functions.
A6. Provide Facility for User to Print Screen
No GUI application is complete without user being able to print a window to show someone else(specially to the programmer who developed it) a screen filled with data or defects on screens such as spelling mistakes of labels missing labels or visual objects not correctly anchored. Unfortunately, New Era does not provide a function to do this and you have to write this using Window APIs (I have written this program on my PC using Visual Basic, which can be called from new era applications with a system call, please ask for a free copy) You may provide this functionality by adding a Print menu item to the preferences menu we discussed above(Hiding Windows) so that all your project’s windows will inherit.
A7. Use of New Era SuperTables and SuperFields
Although New Era super tables(like MS Access) let you write code less database applications, they cannot be used for complex mission critical application, in addition to the fact that they have limitations and problems – and Big memory overhead(Note: In this paper, I am only referring to free form supertable & not grid form ones which we have decided not to use, for various reasons):
* Super fields take up a lot of memory and if you have too many of them, you might get the New Era compilation error “Code segment too Big ” and you will have to split the constructor. If you simply need a text entry box where user can type any text or use a display only text box, having been forced to use a superfield is wasteful.
* A list box inside a super table(and I have used a lot of them) cannot be made a bound control, like a super field bound to a database column. To do this, you must maintain a hidden supper field and a visible list box and manually transfer data between each other. This is wasteful and a burden on the programmer.
* If one of the super fields is a foreign key(say state_code having a value “VIC” for Victoria) and you want the user to see the word Victoria which is read by joining to another table, super table cannot help you. You have to do this manually which reduces the superpower of a supertable to half.
* There are other inherent problems with superfields which I will explain later (and for which we have found solutions so that they have become non problems – but additional work)
* When accessing the database there are extra things(explained in details under the SQL Class later) we may have to do in addition to simply selecting/updating data user wants. Extra things such as automatic logging all database errors to database(if your site decides to do), updating any audit information, applying row/column level security without forcing the programmer to do this. The programmer will simply forget or ignore them to make his life easier and in a large development project, it is not easy to monitor them no matter how hard you try to enforce development standards. Supertables are not the best for these type of implementations.
A superfield, however, has its own strength some of which are built in type checking, and being able to override its validate and data change events in the base class to put your own validations/formatting, saving programmers heaps of coding. I recommend to use superfields only for this purpose, however, as you will see later, you can create a simple frame and use intelligent text boxes and list boxes, to do the same job with much less memory overhead.
A8. Implement All Database Access via a SQL Class
Despite few weaknesses, Connectivity Class of New Era is a piece of art. None of the projects I have been involved in the past, have I been able to develop database applications with so much ease, with minimum or no need to code SQL. This could be done only with the help of a SQL class providing all the database access functions and members holding the name of the table, columns and the key information and master detail information etc. Such a class can cache data retrieved from the database. In fact, the programmer using such a class need not know how to code SQL. The functions within the class generates the SQL dynamically(without syntax errors) and all the basic checks to the data can be done in one (and the best) place before writing to the database. Such a class must provide a series of simple functions(with minimum or no parameters):
o Allow parameters to be passed to the constructor to indicate name of database table, names of columns which your program maintains(indicating which ones form the key) information whether table is a master table or a detail table of a master detail relationship.
o Provide a function to retrieve data from database by constructing the entire SQL with a filter(WHERE clause) built by looking at the information user typed in queriable fields. As I explained earlier, if you name your data entry objects(text boxes etc) to reflect the database column name it represents, and if they have properties such as “UseToFilterRecordsFlag” built into the base class(which you can set in the constructor extension of the window), this function is able to map each database column to screen object and retrieve data and populate the screen. If it retrieves more than one record from a master table, it can bring up a generic window showing a list(build dynamically) for user to select. All the data retrieved is cached within the class. This function must handle all the error reporting so that the programmer does not have to worry about it.
o Provide a function to modify the data in the cache by using data on the screen. Again, the function is smart enough to recognise data in which object on the screen goes to which database column, therefore, it need not have parameters.
o Provide a function to save data cached to the database so that the programmer can call it when the user wants to save.
These are the basic minimum interface from the class to the outside world. There are many other functions which you will figure out when you implement, without my having to tell you now and confuse you.
Advantages of SQL Class
1. Since the SQL class uses dynamic SQL, your application is ODBC compliant(at least theoretically) – this may not be a big deal if you will never change the database engine from Informix, however, this gives the IT manager some comfort !
2. By pasting your visual objects(to which you have built intelligence) on to a simple frame, thereby reducing the overhead of supertables and superfields many times over, the developer can still write “code less” code the same way he can do this using supertables in the same time your having full control over database access.
3. When you access the database, you can do a lot of generic things in addition reading and updating data user is requires. Some of them are:
1. Suppose your site maintains columns in the database for audit purposes(such as who inserted a record and when; who last updated the record and when). And you want to do this without asking the programmer(he may forget or ignore). Every time a record is inserted or updated, you can add the relevant columns to the SQL with complete privacy.
2. You may decide to enforce row and column level security on database records, without depending on the programmer. An example of row level security is that if the employees of the Sales Department are allowed to maintain records of their employees only and when a sales department employee signs on to your system, his profile contains what group he belongs to (in this case Sales) and what kind of restrictions should be applied when he retrieves/updates records – assume that all these information is stored in the database. By looking at this information, you can, in complete privacy, add a where clause to Select & Update SQL(See also window and table level security explained later)
3. A super table handles stale data(i.e. concurrent updates) to let you avoid lost updates. Using a time stamp, which comes as a bonus of part of audit information I mentioned above you can implement this. You can read the time stamp of the record, cache it in the class, and add to the filter when user does the update so that if the timestamp has changed in between the update will fail and you know that data is stale and you can inform the user.
4. You can implement all the database access error and message handling in the class so that even if the developer’s linguistic skills are not the best or even if the SQL class designer’s linguistic skills are in question, text in error messages can be changed in one place. When an error occurs, you have all the information on which field it occurred, what its label is so that you can focus onto the field and add the label to the error message.
5. You can do type checking of data entered, check entries on mandatory/key fields, illegal characters in key fields and duplicate keys etc just before saving a record to the database.
6. Suppose a user brings up a record to a screen having few dozens of visual objects then without changing any entry(or changing one entry and changing his mind and reversing the change) tries to save. You can build the update SQL only to include fields he has changed by comparing with what was retrieved from the database, which is kept in the class. If the user has not made any real changes, show a friendly message and do nothing. Otherwise, build the update SQL length of which is proportional to the number of entries he changed, thereby creating a shorter SQL string to be sent to the UNIX server reducing the network traffic.
7. You can also enforce some special security checks, again in complete privacy, to check if authorised users misuse their power. If Jack has access to run a window which allows to give increment to staff. To make sure that Jack does not exceed his limits and give higher increments to his girl friend thinking that no one will notice, you can build into SQL class to check updates of sensitive columns in a database table such as salary. Every time when this column is updated, SQL class can write to a secret log file in a network drive or log to a database file showing who did and when.
A9. Implementing Menu, Window and Table Level Security
Menu Level Security
Jane is a clerical officer and when she signs on to your system, she will get a menu to call windows which she is allowed to call – “Give Salary Increments” is not one of them, so she does not see it on her menu. But Harry is an officer authorised to give annual increment to staff and when he signs on, he can see the “Give Salary Increments” item on the menu and when he clicks on this, salary increment window displays. This is the menu level security on access to windows – what you see is what you are allowed to see. This can be achieved by assigning groups to users and building hierarchies of menus held in the database with information on which groups have access to them. A generic menu driving window which dynamically creates menus using information in the database when a user signs on can be designed to achieve this. To do this using New Era you need some tricks as the driver program does not know what window to open at compile time – it only knows the name of a variable which at run time contains the name of the window to be called. In New Era, you cannot open up a window using a variable and our trick does this as I will explain under opening a window using a variable – I have to tell you that this trick has some problems which I am investigating.
Table Level Security
A user being allowed to access a maintenance window is one thing and what he is allowed to do inside the window is still another. Mary is answering phone calls from customers and when a customer makes an inquiry about his gas account, Mary opens up a maintenance window, brings up the customer’s account and responds to the query – the story ends there. She cannot change any information on the screen – the data entry fields are display only and save button is hidden. When Marshal, who is a clerical officer, opens the same window, he not only can view the same record but can change data in the fields – the fields are enterable and the Save button is visible. This I call table level security and you can call it whatever you think is better. You can implement this very elegantly by putting code in the constructor extension of your window base class(so that all the windows in your project will inherit). When the window is constructed, code within the constructor looks at the database for permission against the user’s group for the current window and disable fields and hide buttons accordingly. To do this so the programmer will not have to know anything about it, you have to create a vector of all data entry fields, buttons and menus at runtime. I will explain later how to do this.
Column Level Security at Window Level
Suppose in this case the salary of an employee is on the employee maintenance window which also contains other information such as his name and address. When Dick opens this window from a menu, he can see the salary of an employee and can change it as he is authorised to do so. Tom has access to the same window and he can change other things(such as address) but not salary which is display only. When Harry opens the same window he not only cannot change the salary but he cannot see it. Under SQL class, I explained how to enforce column level security in a guaranteed manner. However, visual effects like this make it more user friendly. This can be implemented in similar ways to table level security by building into the base class constructor of the window.
A 10. Provide Generic Windows
The more windows you create, the more windows you have to maintain and bigger will be your machine code which will take more disk space and longer to load. Whenever possible, provide generic windows whose controls are dynamically created at run time. A good example is that if user enters a wild card for a client name which brings several records, which will be displayed on a separate window for user to select the one he is after, use a generic window to which all the display information can be at runtime. Other examples of generic windows are pure table maintenance windows, menu drivers and Report Running windows etc.
A 11. Development Standards.
Development standards are wonderful on paper but implementing them on a large project requires code or peer reviews which is no fun. Programmers will follow standards, without being forced, if the standards make their life easy. If a programmer can develop a maintenance widow in a day by following standards and if the same window requires 3 days if he does not follow standards, he will certainly stick to standards. Furthermore, due to non conformance to standards, the programs will not function as expected or non standard appearance of a window is readily noticeable, then again the programmer will follow the standards. This is the philosophy that we have adopted, by creating visual/SQL classes and generic interfaces and this has paid dividends.
A 12. Compiling Nightmare
Imagine your site has a few dozen applications involving hundreds of windows derived from a single base class. Now if you have made a change to the base class which results in changes to the header file, all the applications should be opened and recompiled. You can avoid manual compilation by writing a batch program which does all the compilation, which you can run overnight. The best solutions to these type of problems is to have automatic source code control system to allow users to check in/out programs and an overnight program takes care of compilation and copying the dll’s to proper places.
A 13. Don’t Do’s in New Era Coding
The list I give below is only a few of many things which I advise you to avoid – this list will grow in time. However, I have to tell you that these are my personal views derived from my own experience and you may have valid, logical reasons to do what I tell you not to do.
* Defining variable as records like a database tables in a database applications is, in my view, enemy number 1, which should never be used:
VARAIBLE MyRecord LIKE MyDatabaseTable.*
You make a declaration like this and later in your code you access the database by building the sql:
SELECT * FROM MyDatabaseTable
After executing this statement and getting an ixRow record, MyRow, you may do this.
LET MyRecord = UNPACKROW MyRow
These 3 statements save a lot of typing(beware: All that Shines is not Gold !). But nobody, may be not even you(after few months) will be able to figure out what is in MyRecord. This kind of code is not readable, which is not the end of the world – the worst is still to come. If some one adds a new column to MyDatabaseTable, which your program need not know, your program has to be forced to recompiled by making some dummy entry such as a blank line to change the time stamp of the 4gl file, otherwise, this code will crash. That is not all, the programmer who has written this code may have left and no one knows which programs to recompile – hunt down all programs or simply wait until the program crashes, when your favourite customer calls for a critical inquiry, and the person answering the phone has to say “Sorry sir, our system is down”. The data independence of relational databases will be thrown out of the window with this kind of coding, which converts your OO program to a COBOL program.
* Another similar pitfall is when you try to insert a record into the database, you may code like this(to save typing):
INSERT INTO MyDataBaseTable VALUES(1,’Harry’, ‘Smith’,…,…)
Instead of specifying the column names like this:
INSERT INTO MyDataBaseTable (Id, FirstName, SurName,…,…)
VALUES(1, ’Harry’, ‘Smith’,…,…)
This will suffer from same flaws as in the case of Record Like(explained above), but in addition, you need to make code change to the insert statement, in addition to just recompiling every time a new column is added to the table, even though the new column is maintained elsewhere – beware of shortcuts, which can be very long.
* It is hard to believe what I am going to say next but believe me this happened. A variable was declared as:
VARIABLE MyVector ixVector() — suppose to instantiate here
When the executable(compiled C code) program containing this line of code ran well on Computer A instantiating the MyVector correctly but the same program run on Computer B did not instantiate leaving MyVector NULL (crashing the program later). The moment we changed the code as follows and recompiled, the program ran successfully on both machines:
VARIABLE MyVector ixVector
LET MyVector = NEW ixVector()
After this experience, I prefer not to use the second type of declaration.
* New Era functions can return more than one parameters:
RETURN A, B, C,………..
The calling program receives the parameters as:
CALL ANY FUNCTION () RETURNING A, B, C,………..
I found this a nuisance when the number of returned values is big and you are required to remember the order. That is my problem but imagine that the function needs to return an additional parameter later which your program is not interested in receiving. Your program needs to be changed and recompiled. To overcome this and many other problems, I declare the function to return an ixVector which contains pairs consisting of parameter name and value and provide another short functions to extract values from the vector. This has made the function, and the code calling it, relatively independent of each other and has proven to be efficient and less cumbersome to programmers.
* Do not have the habit of calling functions like:
LET BoolVar = IsClientOk(VarClienId, “A”, “01/01/1997″)
Call Like this:
LET BoolVar = IsClientOk(
ClientId : VarClienId,
Sataus : “A”,
DueDate : “01/01/1997″
)
The former is dull(lazy), not readable and doomed to fail, specially if you do not include the function prototype, which the compiler will not insist. The compiler will make its own assumptions and if the prototype is not included, the parameters should exactly match by number and position of the actual function.
With the second syntax, code is readable, compiler will force you to include the 4gh file containing function prototype. The order and the number of parameters of the called function is not important as long the parameters you name are present. The greatest advantage is that the called function can add new parameters with defaults(so that you need not pass a value to it) at any position and change the order of the parameters. When this happens, your code will be recompiled due to change in 4gh file with no code changes required.
* Avoid using variables in the window prebody section of a window:
A variable declared in the prebody section of a window (or a module variable in an include file which is included in the prebody section) is in effect a shared variable of the window (although you are not required to use the key word SHARED) and multiple instances of the same window will have only one value for this variable. Unless it is truly shared (in which case declare it as a shared variable or constant within the class extension) do not use it so that you can reference it from within member functions included in the prebody – to save coding. Suppose for instance, to avoid using calls to getWindow() more than once in the “Find Customer” window, you use a variable which holds reference to the window (by calling getWindow() once when the window opens). You declare this variable in the prebody section. Assume your program opens up a second instance of the same window, to find another customer, leaving the first window open. The variable holding the reference now points to the second instance corrupting the first. This example is not very good but gives you the idea. I had heaps of problems with this type of implementations with menu driving generic windows which have multiple instances. To avoid this, use member variables
A14. Allow Runtime Debugging and Diagnostics
Helga calls you and says that when she tries to bring up the customer Paul Klugman, the system says the customer does not exist and she knows that he exists. The programmer who had designed the customer maintenance window has left the project and you are new. You access the database interactively(say using dbaccess) and finds that the PauL Klugman is in the database. How do you know why the SQL in the program fails ? – because the programmer quite correctly added a restriction to the select filter that customer status is active and some one accidentally made Klugman inactive. Problems like this as well as bugs went undetected during testing can be caught by putting a debugging facility. In the preference menu mentioned earlier, you can allow a debug toggle which a user can turn on/off thereby setting up a global debug flag in the memory. In your SQL class or any other base class which implements the database access, you can check if the debug flag is on, if so, display the entire sql which Helga can read out to you or copy and paste into a text file on a public network drive for you to pick up and run interactively. I have implemented this and found it very useful. If you do not want the user to see the SQL for security reasons, you can write it to a temporary file in the public network drive. Because everything is built into the base class, you can add this functionality even after project has been implemented.
A15. Monitor Window Resources
Every window your users open takes up windows resources. The situation becomes worse when they, in addition, have opened other window applications such as Microsoft XL and Microsoft word. When New Era cannot find enough resources when your program instantiates a new window using the NEW operator, New Era closes the application instead of returning a NULL window reference informing the programmer that it runs out of resources. The programmer cannot inform the users that the window cannot be opened(unless in future releases New Era fixes this problem by retruning a NULL reference instead of closing the application.) A good practice, in any event, is for you to monitor the resources and warn the user to close some windows when the amount of free resources goes below a lower limit. You can use the window API’s to monitor the available resources every time a new window is opened. The functionality can be built into you window base class in the constructor extension.
Part B: Problems and Solutions – New Era Development
B1. Calling a New Era Function using a variable
“Why on earth does one want to call a function via a variable ? – simply call it by name”. There are situations, specially when you write generic functions such as running batch programs or jobs using a generic despatcher which at compile time does not know what function to call. It only knows the database table and column name which holds the name of the function. Many GUI products have recognised this problem and provided means to call a function using a variable. The previous GUI product I worked with can call a function using a variable by simply putting a colon (: ) in front of the variable telling the compiler that the name of the real function is the content of the variable – e.g. CALL :FunctionName(). Unfortunately, New Era does not provide any means of doing this. However, thanks to Windows APIs, I have been able to do this very successfully with some tricks which I can tell you. Some of these I discovered accidentally and I am not able to give a logical explanation. First, you must know the dll in which the function resides and the function must reside in a dll and not in an exe. I have been able to implement an automatic job scheduler which calls functions via a variable. If you need sample code, please do not hesitate to email me. To implement this you must know C/C++ & APIs – refer the famous book “Windows API by James L. Conger)
* Read the name of the function to call and its dll name from database or where ever and store it in New Era variable.
* Add an underscore to the front of the name as all new era functions end up being an underscore added to the front.
* Inside BEGIN_C ……………. END_C block, call the API function LoadLibrary() passing the dll name as parameter and get the handle to the loaded dll and then call the API, GetProcAddress() passing the handle to the dll and the name of the function(with underscore in front) as parameters and get the address of the function. Using the address as a pointer, call the function. There is an example in API Bible. Make sure that you pass a single integer parameter to the function which this has to be the number zero.
* After the function returns, use the API call FreeLibrary() passing the handle to the dll to free the dll.
When a New Era global function is converted to C code all its parameters are taken away by the code generator and replaced with a single integer parameter, which is a number and my guess is that this number tells the number of original parameters to the function. New Era passes parameters to the function by pushing to the stack and the function reads them by popping from it. When the function returns, the return values are again pushed to the stack from which the calling function pops – this is my understanding and I may be completely wrong. To not to complicate matters, all the functions, to be called using a variable, must not have any parameters and must return void. If you want to pass data to the function, use either globals(created in a global dll) or the database.
Make sure that the function is called when the user clicks on a button. This is not the whole story. The button you click must be created dynamically on the window using a button class you have created and whose activate event is handled with your own handler and the code to call the function should be inside the handler and never in a function which the handler calls. If you do it in any other way, still everything works well but alas ! When the function which contains the C code containing APIs returns, the system crashes and you will have to restart windows.
B2. Instantiating a Window Using a Variable
This is another why-on-earth… type of question. As I explained under A9, to design a generic menu driving window which reads the menu from the database and create buttons dynamically so that when the user clicks on a button, the appropriate window will be displayed. The code behind the button does not know at compile time what window to be used in the NEW command:
LET WindowVar = NEW MyWindow(…)
Where MyWindow has to be the actual class name of the window which cannot be replaced with a variable. New Era Provide following command:
LET WindowVar = NEW ixWindow(…..) as MyWindowVar
Where MyWindowVar is a variable containing the name of MyWindow class, This line of code creates a blank window – not MyWindow.
The trick that I have found is that in each window, create a global function at the end of 4gl file which accepts no parameters and returns void. All it contains is 3 lines of code to instantiate the window. The name of this function can be made unique by adding some suffix(such as _Instatiate) to the end of the name of the window. If the name of the window is mywin and its class property is MyWinClass and the names of the 4gl is mywin.4gl, the function you create at the end of 4gl file is:
FUNCTION MyWin_Instantiate() RETURNING VOID
VARIABLE TempWin MyWinClass
LET TempWin = NEW MyWinClass(……….)
CALL TempWin.open()
END FUNCTION
This function can be called by the menu driving program the same way I have explained under B1(calling a function using a variable) as the menu program knows the name of the window and its dll. The menu driver is able to construct the name of the function to call by adding “_Instantiate” to the window name. Obviously, creating extra functions is an unnecessary burden on the programmer who develops the window. The best way is to add this function automatically just before compilation by putting in code in the make file. Write a small program which processes the 4gl file, just before New Era’s compiler processes it and add the function. Call to this function can be put in the appropriate place in your Application Builder .INI file.(Again I have written this program in Visual Basic – why VB ? it is easier for file access – and if you need the source code, which obviously has to be changed as I have hardcoded our naming standard to identify the class name, please do not hesitate to ask. )
I have to confess that this approach is not as bullet proof as calling a function using a variable. I have noticed that opening some windows(specially very big ones) through the instantiation function causes the application to hang for no apparent reason. I will continue to investigate and any suggestions from you are welcome. In the meantime, if you want to implement this type of menu interface, I have another method, which is so elegant but does the job.
B3. Creating an Array of Visual Objects of the Window
Again what for ? As I explained under table level security under A9, in order to hide/disable visual objects to(or not to) allow a user to make changes, without expecting every programmer to code for this, the functionality has to be built into the window base class. The base class window, however, does not have access to the visual objects of the windows derived from it. The base class must have a function which is called in the constructor to capture all the controls when the real window is constructed. There is a function GetNextTabEnabledControl() in the ixWindow class which allows you to capture all visual objects starting from a known one such as Exit button which every window must have. However this function cannot catch objects which are disabled or not tab enabled. I have found a better way of doing this. In the .4gh file all the controls are listed in one place. And as in the case of creating an instantiating function(described under B2), you can write a small program to go through the 4gh file and grab the names and build up a string having names of all controls which helps the base class build the vector of visual objects. The program I have written to instantiate the window also does this job in addition – killing two birds with one stone. This vector, along with security information can be used to control access to data entry fields and push buttons.
B3. Some Problems with New Era Superfields
We have noticed that the data changed event of a superfield is triggered only if the user clicks on a list box or a button, immediately after entering data in the superfield. This sometimes is a problem, specially when there is code in the data changed to populate the very list box the user clicked on. The solution to this problem is to create a hidden dummy superfield which is used to set the current cell by calling the SetCurrentCell() function. The code to do this has to be manually put in the focus in event of the non superfield object(list box or text box), which is a nuisance.
The second problem is disabling or hiding the superfield which has the focus. New Era brings an error message “Cannot disable/hide the current superfield” and does not disable/hide the superfield. The solution to this is to create a second dummy superfield which should not be hidden and should not be disabled. This superfield needs to be anchored off the screen so that the user will not see it and it should not be tab enabled so that user cannot tab into it.
These two problems can be solved by putting code into the constructor of the supertable to create the two dummy superfields automatically increasing the number of columns property(numDisplayCols) of the supertable by 2. Having done this, put code in the list box base class’s focus in event to set the current cell to the hidden dummy super field to solve the first problem. To solve the second problem, override the hide and disable events of the superfield base class to set current cell to the second dummy superfield. An obvious side effect of this to the application programmer – to whom the dummies built into the supertable base class must be transparent – is that the supertable member function getNumCols() now returns a number which is more than the number of real non dummy super fields the application developer created. The solution to this is to override this function in the base class to return the number of real superfields after decrementing the total number by the number of dummies. In this way, the problem created in the base class is solved in the base class without allowing it to spread to the descendants.
Conclusion
In conventional, imperative programming, the cost of correcting mistakes made in the early stages of a project and detected in the later stages is exponential. Object Oriented development eliminates this exponentiality, only if you know what you are doing. Spend more time on the design create the necessary building blocks first, thinking of all possibilities – as carpenters say measure twice and cut once !
Tivoli Storage Manager – Informix ontape Backup-Restore Configuration
22 Jul
Posted by ProCOM on July 22, 2007 – 1:50 pm - 2,182 views
Tivoli Storage Manager – Informix ontape Backup-Restore Configuration
Level: Intermediate
Amitava Chakraborty, IBM INDIA
Prerequisites
· Knowledge of Informix Installation along with Server and Database configuration.
· Basic UNIX commands depend upon the OS used.
TSM Server Installation:
Version: TSM Server 5.3.0 and above
In the base directory of CD mount or the un-tar directories of the TSM Server there will be a README file. In this file, the directory structures and the installable software information are there depending upon different OS and their versions.
Note: If older version of TSM already exists then remove the software first then install the new version. The removal commands are also specified in the README file.
Creating required setup at TSM-SRERVER :
Please follow the below mentioned steps as a sequence:
· Go to TSM Server “bin” directory, there is a file called dsmserv.opt which is the configuration file for TSM server. Open the file in a text editor and search for the word “COMMTimeout”. Uncomment the line if it is commented and make the value of this property as 600, which is 10 minutes. This means a client will be connected for continuous 10 minutes while backup/restore. You may have to increase the number depending upon the size of the database and network speed and while you get an error in the administrative console as “Communication time out” while backup-restore. But the value 600 is a big enough value for at least 2 GB Dbspaces.
· Go to TSM Server “bin” directory and start the TSM server by running the command < ./dsmserv> as user root , you will get a prompt like “TSM:SERVER1> “ where “SERVER1” is your TSM server name.
· You can change the name of the server from “SERVER1” to the name you like by running the command
set servername <Your Server Name>
in the administrative console. After running the command immediately your
command prompt will change to “TSM: ’New server name’>”
· Register the license by running the command in the server console.
Register license file= tsmbasic.lic number=10
Make sure the tsmbasic.lic file exists in the current dir otherwise give the full
Path.
· Update the password for admin as your own password from the server console.
update admin admin <password >
· Now create a logical space allocation for taking the backup by using this command at console.
Define stgpool <Logical Pool Name> disk access=readwrite
maxsize=500M
· Now direct the server to store the data in this new logical pool when default backup storage space BACKUPPOOL is full by using this command.
Update stgpool BACKUPPOOL next stgpool=<Logical Pool Created>
· Now create the physical storage space under the logical pool
define volume <name of the logical pool created>
<path with name , where the space will be created> access=readwrite
formatsize=500
· Now make sure that the space has created successfully by running the command
Query stgpool
Query volume
· Now register the TSM client machine by running the command
register node < client node name > < admin password for user admin >
domain=STANDARD
Now your server is setup for Informix-Ontape backup.
Note:You can find these server commands more in details in the link
http://publib.boulder.ibm.com/tividd/td/SMAS4N/GC35-0316-01/en_US/HTML/a454rf02.htm#ToC_335
TSM client and Informix should be installed in the same machine.
TSM Client Installation:
Version:
· For aix32, aix64, hpux32, hpux64, linux32, solaris32, and solaris64 TSM Client 5.2.0 or later
· For Windows 32 and zlinux64 TSM Client 5.2.2 or later
· For ppc64 and Linux on x86_64 TSM Client 5.3.0 or later
In the base directory of CD mount or the un-tar directories of the TSM Client there will be a README file. In this file, the directory structures and the installable software information are there depending upon different OS and their versions. Under those directories you will get the required information to install the TSM Client. For Informix Back-up and Restore through ontape, TSM Backup & Achieve Client installation is required. No need to install the TSM API Client.
Informix Installation:
Please follow the Informix Installation guide to install the Informix on the required machine. Create an Informix Server and test databases on which the backup and restore will be performed.
Configuring the TSM Client for Ontape Backup-Restore:
Environment settings:
—————————————
Please ensure that the below settings are done at your environment:
INFORMIXDIR <Informix Dir>
PATH $INFORMIXDIR/bin:.:/usr/local/bin:/bin:/usr/sbin:$PATH
INFORMIXSERVER <Instance Name>
ONCONFIG <onconfig file>
INFORMIXSQLHOSTS $INFORMIXDIR/etc/<sqlhost file>
DBSERVERNAME <server name>
DSM_DIR <tsm client/ba/bin >
DSM_LOG /tmp/tsmlog
DSM_CONFIG $DSM_DIR/dsm.opt #< You may have to copy the dsm.opt.smp to dsm.opt (if not exists) >
PATH <tsm client/ba/bin>:$PATH
TERMCAP <Informix Dir>/etc/termcap
Parameters settings for Informix onconfig file:
# System Archive Tape Device
TAPEDEV <Informix Chunk Path >/tapedev # Tape device path
TAPEBLK 16 # Tape block size (Kbytes)
TAPESIZE <Value of LOGSIZE> # Maximum amount of data to put on tape (Kbytes)
# Log Archive Tape Device
LTAPEDEV <Informix Chunk Path >/ltapedev # Log tape device path
LTAPEBLK 16 # Log tape block size (Kbytes)
LTAPESIZE <Value of LOGSIZE> # Max amount of data to put on log tape (Kbytes)
Note: Please do not mention the same name for both tape device.
After making these changes you have to bounce the Informix server to take effect of these new settings.
Parameters settings for DSM_CONFIG file:
servername <TSM server name>
Now in the $DSM_DIR search for file named dsm.sys. If not exists copy the dsm.sys.smp to dsm.sys
Now change the dsm.sys
SErvername <TSM server name>
COMMMethod TCPip <do not change>
TCPPort 1500 <do not change>
TCPServeraddress <TSM server ip address>
PASSWORDACCESS GENERATE <- add this line if not there.>
Check the permission of /tmp/tsmlog , the directory tsmlog must be 777. Then create a file dsierror.log and change the file permission to 777.
IMP: After making these changes, please run the command dsmc as super user.It will ask for TSM Server user id and password .Please use user id “admin” and password as same as we set during the TSM server configuration. Once you enter all those correctly, it will enable you to login and give you a “tsm>” prompt. Please enter the command “quit” to come out from this prompt. Now as “Informix” user, please run the command dsmc, this time it should not asks for any user verification and directly prompt you as “tsm>”.Use command “quit” again to come out from the “tsm>”
IBM Tivoli Storage Manager
Command Line Backup/Archive Client Interface
Client Version 5, Release 3, Level 4.0
Client date/time: 09/14/06 09:44:36
(c) Copyright by IBM Corporation and other(s) 1990, 2006. All Rights Reserved.
Node Name: SUN3500.LENEXA.IBM.COM
Session established with server SERVER1: Linux/i386
Server Version 5, Release 3, Level 2.0
Server date/time: 09/14/06 09:40:25 Last access: 09/14/06 09:40:17
tsm>
Installing the Ontape Backup-Restore Patch:
Please copy these 3 files to the directory $INFORMISDIR/bin
Ontape Backup-Restore through TSM:
Backup
Pre-Condition: Database Server is online mode
Archive full system
Go to Informix Log directory. Now run the command
ontape_backup.sh | ontape –s | tee auto_ontape.log
You do not have to press any option, every thing is automated .You can keep look into your terminal to know what is happening .Sometimes you may see some error like “Bad tape”, the program is intelligent enough to take care of those error. You should receive the message like this in your terminal:
sun3500% ontape_backup.sh | ontape -s | tee auto_ontape.log
Please enter the level of archive to be performed (0, 1, or 2)
Please mount tape 1 on /usr/informix/data/tapedev and press Return to continue …
Tape is full …
Please label this tape as number 1 in the arc tape sequence.
This tape contains the following logical logs:
7
Please mount tape 2 on /usr/informix/data/tapedev and press Return to continue …
Tape is full …
Please label this tape as number 2 in the arc tape sequence.
Please mount tape 3 on /usr/informix/data/tapedev and press Return to continue …
Tape is full …
Please label this tape as number 3 in the arc tape sequence.
Please mount tape 4 on /usr/informix/data/tapedev and press Return to continue …
Tape is full …
Please label this tape as number 4 in the arc tape sequence.
Please mount tape 5 on /usr/informix/data/tapedev and press Return to continue … 100 percent done.
Please label this tape as number 5 in the arc tape sequence.
Program over.
sun3500%
Continuous backup of logical logs
Go to Informix Log directory. Now run the command
ontape_backup.sh | ontape –c | tee auto_ontape.log
You do not have to press any option, every thing is automated .You can keep look into your terminal to know what is happening .Sometimes you may see some error like “Bad tape”, the program is intelligent enough to take care of those error. As soon the logical log fills the tape, it will take the backup and transfer the backed up tape files to tsm. You should receive the message like this in your terminal:
sun3500% ontape_backup.sh | ontape -c | tee auto_ontape.log
Performing continuous backup of logical logs.
Please mount tape 1 on /usr/informix/data/ltapedev and press Return to continue…
Tape is full …
Please label this tape as number 1 in the log tape sequence.
This tape contains the following logical logs:
7 (partial)
Please mount tape 2 on /usr/informix/data/ltapedev and press Return to continue…
Tape is full …
Please label this tape as number 2 in the log tape sequence.
This tape contains the following logical logs:
7 – 8
Please mount tape 3 on /usr/informix/data/ltapedev and press Return to continue…
Tape is full …
Please label this tape as number 3 in the log tape sequence.
This tape contains the following logical logs:
8 – 9
Please mount tape 4 on /usr/informix/data/ltapedev and press Return to continue…
Tape is full …
Please label this tape as number 4 in the log tape sequence.
This tape contains the following logical logs:
9 (partial)
Please mount tape 5 on /usr/informix/data/ltapedev and press Return to continue…
Note: For Automatic backup of the logical log, Please use ontape_backup.sh | ontape –a | tee auto_ontape.log
Restore
Physical restore for HDR
Pre-Condition: Database Server is in shutdown mode
Go to Informix Log directory. Now run the command
ontape_p_restore.sh | ontape –p | tee auto_ontape.log
It will show you the list of 1st tape drive from different full system backup taken on different days and time .You have to choose one of them which is required and press enter and then you do not have to press any option it will bring the different backup tapes from TSM Server and restore them. The terminal message will be like this only:
sun3500% ontape_p_restore.sh | ontape -p | tee auto_ontape.log
Please mount tape 1 on /usr/informix/data/tapedev and press Return to continue .
.. /usr/informix/data/main950.09-12-2006-06:16.1.tapedev
/usr/informix/data/main950.09-12-2006-06:24.1.tapedev
/usr/informix/data/main950.09-12-2006-06:28.1.tapedev
/usr/informix/data/main950.09-12-2006-06:36.1.tapedev
/usr/informix/data/main950.09-12-2006-06:38.1.tapedev
/usr/informix/data/main950.09-12-2006-06:45.1.tapedev
/usr/informix/data/main950.09-12-2006-06:48.1.tapedev
/usr/informix/data/main950.09-12-2006-08:09.1.tapedev
/usr/informix/data/main950.09-14-2006-05:11.1.tapedev
/usr/informix/data/main950.09-14-2006-05:25.1.tapedev
/usr/informix/data/main950.09-14-2006-06:40.1.tapedev
Please enter the file number with path
/usr/informix/data/main950.09-14-2006-06:40.1.tapedev (I choose this file)
Files retrieved successully from tsm …
Archive Tape Information
Tape type: Archive Backup Tape
Online version: IBM Informix Dynamic Server Version 7.31.UN233
Archive date: Thu Sep 14 06:40:38 2006
User id: informix
Terminal id: ?
Archive level: 0
Tape device: /usr/informix/data/tapedev
Tape blocksize (in k): 16
Tape size (in k): 400
Tape number in series: 1
Spaces to restore:1 [rootdbs ]
Archive Information
IBM Informix Dynamic Server Copyright(C) 1986-2004 IBM Informix Software, Inc.
Initialization Time 09/14/2006 06:38:50
System Page Size 2048
Version 6
Archive CheckPoint Time 09/14/2006 06:40:38
Dbspaces
number flags fchunk nchunks flags owner name
1 1 1 1 N informix rootdbs
Chunks
chk/dbs offset size free bpages flags pathname
1 1 0 10000 4663 PO- /usr/informix/data/rootdbs
Continue restore? (y/n)Do you want to back up the logs? (y/n)
Please mount tape 2 on /usr/informix/data/tapedev and press Return to continue … Tape sequence number is 1, expecting 2
Bad tape.
Please mount tape 2 on /usr/informix/data/tapedev and press Return to continue .
..
Please mount tape 3 on /usr/informix/data/tapedev and press Return to continue .
..
Please mount tape 4 on /usr/informix/data/tapedev and press Return to continue … Restore a level 1 archive (y/n)
Program over.
sun3500%
Logical restore for HDR
Pre-Condition: Database Server is in Fast Recovery mode ( Physical restore Completed)
1. Open 2 sessions and set the environment for both sessions
2. Now from one session run the command
ontape –l
It will flash you the message like :
Roll forward should start with log number 7
Please mount tape 1 on /usr/informix/data/ltapedev and press Return to continue…
3.Now from another session run the command ontape_l_restore.sh
It will list you all the logical logs back-up.Please choose the correct one and press enter.It will mount the desired tape on the logical tape device. And Confirm you on that.The message will flash in the screen like this way:
/usr/informix/data/main950.7(partial).ltapedev
/usr/informix/data/main950.7-8.ltapedev
/usr/informix/data/main950.8-9.ltapedev
/usr/informix/data/main950.9(partial).ltapedev
/usr/informix/data/main950.Pleaselabelthistapeasnumber7inthearctapesequence..lta
pedev
Please enter the file name to restore
/usr/informix/data/main950.7(partial).ltapedev ( I enter this logical log)
IBM Tivoli Storage Manager
Command Line Backup/Archive Client Interface
Client Version 5, Release 3, Level 4.0
Client date/time: 09/14/06 09:17:21
(c) Copyright by IBM Corporation and other(s) 1990, 2006. All Rights Reserved.
Node Name: SUN3500.LENEXA.IBM.COM
Session established with server SERVER1: Linux/i386
Server Version 5, Release 3, Level 2.0
Server date/time: 09/14/06 09:13:10 Last access: 09/14/06 09:13:01
Restore function invoked.
Restoring 409,600 /usr/informix/data/main950.7(partial).ltapedev [Done]
Restore processing finished.
Total number of objects restored: 1
Total number of objects failed: 0
Total number of bytes transferred: 400.07 KB
Data transfer time: 0.02 sec
Network data transfer rate: 15,086.17 KB/sec
Aggregate data transfer rate: 131.65 KB/sec
Elapsed processing time: 00:00:03
File restored successfully
File mounted on tapedev … Please continue the backup
Press <q> when you are finished else press enter ..
4. After getting the confirmation message like “File mounted on tapedev … Please continue the backup” , Please press enter for the session showing “ Please mount tape 1 on /usr/informix/data/ltapedev and press Return to continue…”
5.Log Back up will be restore and after the restore is complete it will flash a message “Do you want to restore another log tape? (y/n)” , If you wish please press Y else N.
On Pressing Y , it will show the Message like this “Please mount tape 2 on /usr/informix/data/ltapedev and press Return to continue…” and pressing N it will come out saying “Program Over” and put the database server as “Quotient” mode.”
6.If you want more logical log restore , Press enter to the 2nd session, It will again show you the list of logical logs and you have to choose the correct one , after mounting the logical log on Log tape device correctly you should press enter in the 1st session to restore the mounted log. And repeat the step 5, 6 as soon as your restoration will be in progress.
Ontape simple test:
Please do a simple test to make sure ontape is working fine.
1.Backup data through ontape by running the command ontape_backup.sh | ontape –s | tee auto_ontape.log.
2.Now start the continues log backup by ontape_backup.sh | ontape –c | tee auto_ontape.log.
3.Make some transaction so that the atleast one logical log fulls
4.Make the Informix-Server down.
5.Restore the database by running the command ontape_p_restore.sh | ontape –p | tee auto_ontape.log
6.Restore the database by running the command ontape_p_restore.sh | ontape –p | tee auto_ontape.log
7.Restore the backed up logical log with the combination of the command ontape_r_restore.sh and ontape –l as described in the document
7.Make the Informix server online and try to see the changes you made after the archive backup.
| By Pan Pantziarka | + More by this author |
Java’s own open source database
It sometimes feels as if open source databases are a dime a dozen. There are the big names like MySQL, PostgreSQL, and Ingres. There are plenty of other lesser known but no less powerful open source databases: Firebird, SQLite, HSQLDB, Apache Derby, IBM Cloudscape, and Java DB.
Actually, I cheated there at the end, those last three are the same database sporting different branding. Apache Derby – which is the name we’ll stick with throughout the rest of this article – used to belong to Informix, who had bought a company called Cloudscape, who had developed a SQL-compliant Java relational database. Informix was in turn swallowed up by IBM, who eventually open sourced Cloudscape by contributing the code to Apache Software Foundation. Cloudscape then became Apache Derby; though when Sun Microsystems decided to add it to the Java 6.0 SDK, they christened it the Java DB.
Now that we’ve got all that clear, what actually is Apache Derby and why should you be interested? Derby is a relational database management system written completely in Java. It offers a high level of SQL standards-compliance, native access using JDBC, works both as an embedded database or in client/server mode and has a relatively small footprint (around 2MB).
But it’s not just compact, it’s powerful too, and supports transactions, referential integrity, stored procedures (written in Java), and in client/server mode supports bindings to ODBC, PHP, Perl, and Python. Being Java also makes it multi-platform, and it uses any certified Java Virtual Machine so that its availability is maximised across platforms.
This tutorial will concentrate mainly on using Derby as an embedded database for a Java application. In other words Derby will be used as a persistent data store, and it is the application which will manage the database. This is in contrast to client/server mode, where the database is loaded onto a server and waits for client applications to connect to it.
Installation
Installation is remarkably painless. It’s a simple case of downloading the zip files (fromhere) and unzipping to an appropriate directory. We can easily check that installation has been successful by using the sysinfo tool in the bin directory (sysinfo.bat for Windows). To do this requires the setting up of an environment variable called DERBY_HOME to point to the bin directory, and for this to be added to the PATH. For example under Windows:
set DERBY_HOME=C:\Apache\db-derby-10.2.1.6-bin set PATH=%DERBY_HOME%\bin;%PATH%
With these environment variables in place running sysinfo at the command prompt will spool out a listing of information about the Java environment and the installation of Derby that it has found.
Command-line Access
Sticking to the command-line for the moment, we can use the ij tool to interact with Derby. This provides a command-line from which we can connect to a database instance and issue SQL commands to create tables, enter data, and submit queries.
The first thing we’ll do is create a new database, called AssetDB, which we’ll use later with some Java code. It will be a simple DB with just two tables to hold user names and a basic asset register. Assuming that we want the database to be in the C:\DerbyDB directory we create it by loading the ij command-line (type ij from a command-prompt), and then at the ij> prompt entering:
connect 'jdbc:derby:/DerbyDB/AssetDB;create=true';
This creates the directory tree C:\DerbyDB\AssetDB. To populate the database we can either enter the CREATE TABLE statements direct from the ij> prompt, or we can run them from a text file. We’ll do the latter and enter the following commands into a file called create_sql.txt in the DerbyDB directory:
CREATE TABLE USERS ( FIRST_NAME VARCHAR(30) NOT NULL, LAST_NAME VARCHAR(30) NOT NULL, EMP_NO INTEGER NOT NULL CONSTRAINT EMP_NO_PK PRIMARY KEY ); CREATE TABLE PC ( TYPE VARCHAR(10) NOT NULL, SERIAL VARCHAR(50), OS VARCHAR(20), EMP_NO INTEGER, CODE_NO INTEGER NOT NULL CONSTRAINT CODE_NO_PK PRIMARY KEY );
To run these from ij> enter the following command: run '/ DerbyDB/create_sql.txt';
The SQL commands will be executed in turn and ij will report that ‘0 rows have been inserted/updated/deleted’. Running describe USERS; will produce a listing of the columns and column meta-data for the table.
We can also use ij to enter some data. Again we’ll use a text file to contain the following INSERT queries:
INSERT INTO USERS VALUES('Bill','Gates',1);
INSERT INTO USERS VALUES('Joe','Bloggs',2);
INSERT INTO USERS VALUES('Peter','Kropotkin',3);
INSERT INTO PC VALUES('Desktop','01010','Linux',1,1);
INSERT INTO PC VALUES('Laptop','101010','BSD',2,2);
INSERT INTO PC VALUES('Desktop','101010','XP',3,12);
Finally, we can run a SELECT query: SELECT * FROM USERS;
To quit ij simply enter: exit;
And Java?
So far we’ve used a command-line tool to create a simple database and enter a few rows of data; big deal. Now we’ll show how simple it is to embed that database in a Java application. The first step is to make sure that the derby.jar file is on your classpath or is included with your project in Eclipse, NetBeans or other IDE.
Setting up the database just uses standard JDBC functionality. Register the JDBC driver, provide a URL for the connection, and attempt the connection:
String driver = "org.apache.derby.jdbc.EmbeddedDriver";
String dbName="/DerbyDB/AssetDB";
String connectionURL = "jdbc:derby:" + dbName;
Connection conn = null;
try{
Class.forName(driver);
} catch(java.lang.ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(connectionURL);
//body of code to go here
} catch (Throwable e) {
e.printStackTrace();
} finally {
conn.close();
}
Once the connection is made it’s possible to create a statement object and to use it to access the database. Here we want to add another record to the database:
Statement st=conn.createStatement()
int m=st.executeUpdate("INSERT INTO USERS VALUES('Adam','Smith',4)");
System.out.println("Updated " + m + " rows");
Executing the above should result in the message “Updated 1 rows” appearing in console output.
SELECT queries are no more difficult:
ResultSet rs=st.executeQuery("SELECT * FROM USERS");
while (rs.next()){
String first=rs.getString("FIRST_NAME");
String last=rs.getString("LAST_NAME");
System.out.println("Name: " + first + " " + last);
}
rs.close();
Joins are straightforward too:
String qry="SELECT USERS.FIRST_NAME, USERS.LAST_NAME,"
+ " PC.OS FROM USERS, PC WHERE USERS.EMP_NO=PC.EMP_NO"
+ " AND USERS.EMP_NO=1";
rs=st.executeQuery(qry);
while (rs.next()){
String first=rs.getString(1);
String last=rs.getString(2);
String os=rs.getString(3);
System.out.println(first + " " + last + " uses " + os);
}
rs.close();
This prints out the message: Bill Gates uses Linux
Conclusion
If you add to its simplicity of setup, ease of use, and small footprint, the fact that Derby supports referential integrity and ACID-compliant transactions, you have a pretty powerful database tool for your Java applications.
Translator
 |  |  |  |  |  |
 |  |  |  |  |  |
 |  |  |  |  |  |
 |  |  |  |  |  |
 |  |  |  |  |  |
 |  |  |  |  |  |
 |  |  |  |  |  |
| By N2H | |||||
Blogs We Read
Recent Articles
Recent Comments
- The Three Advantages of VoIP: Mobility, Convenience and Cost | Beni's Blog on The Three Advantages of VoIP: Mobility, Convenience and Cost
- VoIP Connection Problems & Testing | Beni's Blog on VoIP Connection Problems & Testing
- VOIP Advantages and Disadvantges | Beni's Blog on VOIP Advantages and Disadvantges
- Secure Voice Communication through Secure Telephone | Beni's Blog on Secure Voice Communication through Secure Telephone
- How Much Bandwidth is Required for VoIP Phones? | Beni's Blog on Disadvantages of VOIP
Tag Cloud
Options
Categories
- Advertising
- AJAX
- Apache
- Apache Derby
- APIs
- C++
- C++ Tutorials
- cPanel
- CSS
- CSS Advanced
- CSS Beginner
- CSS General
- CSS Intermediate
- CSS Tutorials
- Digest Dump
- Digital Photography
- Free Time
- Freeware
- fun
- Funny
- General Coding
- Graphic Art
- Greetings
- Hardware
- How To
- HTML
- HTML Advanced
- HTML Beginner
- HTML Intermediate
- HTML Tutorials
- Informix
- Informix Tutorial
- Internet
- Java
- Javascript
- Javascript Tutorials
- Jokes & Fun
- Life Style
- Make Money
- Marketing
- MS Access
- MySpace Tutorials
- MySQL
- News
- Opinions
- Oracle
- PHP
- PHP Tutorials
- Politics
- Poster
- Privacy
- Quick Tips
- Review
- Ruby-On-Rails
- Security
- SEO
- Social
- Social Networking
- Software
- Sports
- SQL
- strange facts
- T-SQL
- Tapestry Framework
- Tech & Business
- Technology
- Travel Destinations
- Tutorials
- Uncategorized
- Vintage
- VoIP World
- WEB
- Web Authoring
- Web Design
- Web Resources
- Windows
- Windows Vista
- XML