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

SQL Plan Cache Snapshot, Visual Explain, and other tools solve a DB2 for i5/OS performance mystery.
By Jim Flanagan and Shantan Kethireddy

The story in this column illustrates a methodology DBAs can use to solve SQL query performance issues in DB2 for i5/OS V5R4 when crucial evidence is missing.

Mr. D.B. Admin of Company A wants to hire famed detective Shantan Kolmes and his sidekick Dr. Flatson to help with a serious performance problem. As of last week, Company A’s main order application has been experiencing drastic performance degradation. Mr. D.B. Admin calls Kolmes and Flatson to explain the problem.

“Every day at 10 a.m., our customers’ orders are at their peak, and our operators can’t process them fast enough. We’ve had customers disconnect mid-order. We’ve lost several long-time customers and who knows how many new customers. Our chief competitor, Company Z, is quickly taking over several of our accounts. Performance wasn’t an issue until very recently. Please help — the CEO is ready to fire the entire IT staff!”

“Do you have a SQL Performance Monitor from when the system was running well?” asks Kolmes.

“No, we didn’t see the need for it.”

“I suppose you don’t have an SQL Plan Cache Snapshot either?” Kolmes says.

“No, does that matter?”

“It will make things a bit more difficult.” But Kolmes remained confident. “Has anyone changed the application since you started seeing this problem?” Kolmes asks.

“No, all changes are supposed to be approved through me.”

Kolmes finds Mr. Admin’s choice of words curious. “You say, ‘supposed to.’ It sounds like you might have had the same issue in the past?”

“Well, yes. I have one developer who is a bit of a maverick. But he assures me he didn’t change anything.”

“Have you noticed anything else unusual?” Kolmes asks.

“We did have a contract programmer recently for a minor update, but I approved that change myself.”

Kolmes says he’ll arrive before 10 a.m. the next day. “Whatever you do, please don’t IPL the system!”

In the meantime, Flatson researches Company Z. As he looks through his notes from the questioning of Mr. Admin, Kolmes begins to develop a preliminary strategy for clue collection and analysis.
Mr. Kolmes and Dr. Flatson arrive at Company A at 9 the next morning. Mr. Admin reports that he successfully stopped the system operator from performing an IPL of the system.

Snapshot Significance

If you’re in the situation facing Mr. Kolmes, the first order of business is to capture the SQL performance environment by taking a SQL Plan Cache Snapshot. To do so, go to the iSeries Navigator interface, expand the Databases folder and the specific database folder, and then right-click it. Select the SQL Plan Cache option and then the Show Statements menu action.

On the statements screen, you can fill in the filters you’re interested in. In the Kolmes case, we know which application is causing the problems, so you’d fill in the schema name for the queries that use or reference these objects. You can also set the filter for Top ‘n’ queries with the largest total accumulated runtime, if you were sure you’d catch the queries for KEYPROD1. Figure 1 shows what Kolmes retrieved. You can save the list of queries to a schema for later viewing by choosing Create Snapshot and giving the schema and snapshot name.

Figure 1. List of queries referencing KEYPROD1.

Figure 1 shows the names of the contract programmer, Mr. Admin, and the maverick programmer. But row six shows something interesting; the User Name in that row matches the last name of Company Z’s CIO.

Notice how easily we (and the two detectives) uncovered preliminary evidence. Before V5R4, you had to turn on a monitor during a period when the application was experiencing difficulties and then analyze the monitor data using monitor analysis queries. This approach could cause system performance degradation if appropriate filtering wasn’t used. You also had to try to duplicate the environment in which the application was running. Now, you can simply take a SQL Plan Cache Snapshot with no system overhead, and have all of the same information and analysis tools at your disposal. Keep in mind that the SQL Plan Cache only contains information about queries that went down the SQL Query Engine (SQE). In-depth analysis of queries that went down the Classic Query Engine (CQE) may still require use of the Database Monitor. See Resources for more information about the difference between SQE and CQE.

If we had had a SQL Plan Cache Snapshot or a monitor from before the point of the initial degradation, we could have run the comparison menu action between the old and new environment to see what was changed. Instead, we’ll need to analyze the worst running queries from the application under the present conditions, figure out the root cause of the performance bottleneck, and then try to figure out what changed (and why) to cause this bottleneck.

A Visual Clue

In order to view the detailed performance information for a given query, you would use a tool called Visual Explain. Visual Explain is a database tool that graphically represents the optimizer implementation of a query request. It provides a method of identifying and analyzing database performance problems. The tree-like output provides a comprehensive understanding of the optimized plan. Visual Explain shows all the attributes necessary for users and developers to understand implementations, performance bottlenecks, indexing strategies, and so on.

Going back to the SQL Plan Cache viewer dialog, you can simply select the desired statement and click Run Visual Explain.

The Final Select icon shows all environmental attributes such as pool size, number of processors, QAQQINI information, and so on. Based on the list of environmental settings Mr. Admin provided, the detectives find these attributes to be in order. Mr. Admin’s company doesn’t have SMP installed, so the lack of parallel processing is expected. You can see the area of the tree that was estimated to be the most costly by going to the View menu and selecting the Highlight Index Advised menu option. In Mr. Admin’s case, the most costly area of the tree was a table scan on the two-billion-row ORDERS table, which populated a hash table used for joining.

The Index Advisor (available as an icon in the toolbar) suggests indexes to improve performance. In Mr. Admin’s case, the Index Advisor suggests an index over a heavily used join column from the two-billion-row ORDERS table, which would explain the poor performance.

If you look at the rest of Mr. Admin’s poorly performing statements, you see that all the statements reference the same join column from the same ORDERS table. To find out if this missing index is behind the performance problems, you have to determine whether this index, or a comparable index, had existed before the performance degradation and that it was removed immediately prior to the degradation.

Reconstructing Details

To find a record of objects in the database from a period when the application was running well, you would open the iSeries Navigator application and expand the Systems folder. Right click on the database folder and select the Health Center menu option. You should see the Overview page. Click on the Size Limits tab. In Mr. Admin’s system, the settings are already set to the schema the application is running in. Hit Refresh to show the current size limits for KEYPROD1. If you want to save the results on this screen, choose save, and the save dialog displays with the history file name filled in.

Now that the current environment is saved to the history file, you can do a comparison to the last capture. Click View History… and fill in the history filename.

When Mr. Kolmes sees the history file that contains the two collections of the Size Limits for this application, he expands the Maximum number of index entries node in the two collections so that he can compare the history files of the current and last months (see Figure 2).

Figure 2. Comparison of current and past history files.

His examination shows that there is one less index in the current environment than in the previous environment. The missing index is IMPORTANT_IDX1.”

Kolmes tells Flatson that they need one final piece of data: the auditing journal (QSYS/QAUDJRN). Using the Display Journal (DSPJRN) command on the auditing journal reveals the precise moment when the index was removed — and who removed it.

Who Done It?

Mr. Kolmes calls a meeting with Company A’s CEO, Mr. Admin, the system operator, and the maverick programmer.

“We found a DROP INDEX entry in the audit journal. Does anyone know why that was done?”

The system operator says it sounds just like the maverick programmer.

Kolmes replies, “We thought that might be the case, but the queries in the SQL Plan Cache Snapshot show your User Name, Mr. System Operator.”

“That’s not unusual; I do queries when diagnosing a system problem.”

“Ah, but your name also shows up in the audit journal as the User Name who did the DROP INDEX. We also found out that the CIO of Company Z has a nephew that works in this company,” Flatson chimes in.

The system operator yells out, “It was me alright, I admit it! I would have gotten away with it too if it hadn’t been for those new tools, and if you hadn’t stopped me from IPLing the system.”

“Tell it to the judge,” Kolmes replies.

---

In Mr. Admin’s case, a company wanted to steal a competitor’s business, so they used an insider to remove a key index. Once the index is added back, order processing performance should return to its expected sub-second timeframe.

The moral of the story? Keep an SQL Plan Cache Snapshot of your environment so you have something to check against if you encounter performance problems in the future.

Jim Flanagan is an advisory software engineer at IBM in database development for DB2 for i5/OS and team leader of iSeries Navigator — Database.

Shantan Kethireddy is a member of the IBM SQL Query Engine team and a frequent speaker at System i technical conferences.

• 0 Comments

Sleek, simple, and efficient, DB2 9 Data Warehouse Edition promises a modern enterprise warehouse without the hassle.
By Bob Rumsby

Analysts across many industries — retail, banking, insurance, healthcare, telecommunications, government — need timely, accurate, and new information about their businesses to help them prosper in the marketplace. Retail companies, for example, collect huge amounts of transactional data about daily sales activity. But simply having that information isn’t enough. They need to know why sales have dropped for a particular brand or store and how to promote new products in the most cost-effective way.

Data warehousing project managers have tried to address these requirements for a long time — at great cost. They have had to manage the purchasing, maintenance, and learning curve of pulling together multiple products from multiple vendors. What if the software infrastructure and tools to solve these kinds of business problems were all under one roof? What if customers could:

• Adopt a single design and deployment tool for data warehouse and OLAP development
• Eliminate custom-developed SQL scripts that maintain warehouse tables
• Simplify and accelerate the discovery of new business insights from data mining
• Create interactive portals, with custom reports based on relational warehouse data.

DB2 Data Warehouse Edition Version 9.1 (available since mid-2006 on Linux, Unix, and Windows platforms) responds to these questions by providing the tools to build and maintain DB2 enterprise data warehouses. DWE 9.1 also supports analytic structures required for data mining and online analytic processing (OLAP) and the development of embedded analytics for business access on the Web.

DWE 9.1 brings many functional components together and exposes many of their functions through two common client tools: the Design Studio and the Administration Console. DWE components include:

• DB2 UDB Enterprise Server
• Database Partitioning Feature (DPF)
• WebSphere Application Server
• SQL Warehousing Tool
• Rational Data Architect (RDA) physical data modeling
• Intelligent Miner
• Cube Views (OLAP)
• DB2 Alphablox
• DB2 Query Patroller.

DWE Design Studio

The Eclipse-based Design Studio is an integrated graphical development environment for designing:

• Physical data models, including DDL and SQL generation and deployment features, JDBC connectivity to data sources and targets, comparison and synchronization of database objects, and basic metadata support for modeling SQL-based data flows and mining flows (functions derive from the RDA tool set)
• SQL-based flows, including both data flows that perform transformation functions inside the DB2 database and mining flows that perform easy data mining discovery and visualization
• Control flows, which sequence data flows and mining flows, integrate external commands, programs, and stored procedures, and provide conditional processing logic
• OLAP cube models, including recommendations for materialized query tables (MQTs).

The Eclipse Workbench provides some inherent benefits, including import and export capabilities for project management, online update features for new plug-ins, a dynamic help system, and the ability to integrate a ClearCase or CVS-based version control system.

DWE Administration Console

The Administration Console is the DWE Web client, providing a common administration environment for data warehouse applications. The console is hosted by WebSphere Application Server and recognizes data sources and system resources already defined in the WebSphere environment.

Applications and models that warehouse architects have developed and tested in the Design Studio are deployed to WebSphere, where administrators and operators can schedule and monitor jobs on production systems.

These two new tools make key tasks much easier for warehouse architects and administrators. I’ll explain how these tools make some key tasks easier.

Physical Data Modeling

RDA-based physical data modeling features provide the metadata foundation for warehouse sources and targets. You can reverse engineer a model from either a DDL script or a database connection. You can also create new database objects, add constraints, and compare the structure of different objects.

After creating or reverse-engineering the model, you can drag and drop tables and other objects into data flows and mining flows. You can also connect to live data sources via JDBC, sample the contents of tables, and deploy generated DDL scripts (see Figure 1).

SQL Warehousing Tool

Data warehouse architects use the SQL Warehousing Tool (SQW) to build and maintain database tables and other analytic structures. Design functions are exposed to users in the Design Studio; deployment and scheduling functions are exposed in the Administration Console. The driving force behind these functions is a SQL code generation system optimized for the DB2 engine.

SQW data flows provide an extensive set of data transformation “operators.” The core transformations correspond to SQL operations, such as table joins, UNION, and GROUP BY. Several operators offer specialized warehouse building capabilities, such as slowly changing dimension updates, key lookups on dimension tables, and fact key replace operations that update surrogate keys in a warehouse fact table (see Figure 2).

Data flows are easy to develop as graphical free-form assemblies of operators, with specific properties defined at each step (see Figure 3). Reusable “subflows” can be created to capture units of work that can be shared across flows; interim results can be staged at different points in a flow; and the generated code can be tested and inspected before production use. You can define variables for properties such as schemas, tables, and files, then supply the correct information at runtime, based on the specific requirements of the job.

You can set properties to take advantage of the Database Partitioning Feature for expensive operations such as select distinct processing and key lookups. SQW can generate bulk loads from flat files or relational sources, using the native DB2 LOAD and MERGE utilities. For example, the code in Listing 1 uses a SQL merge operator to perform an “upsert” operation into a fact table based on two conditions that define the criteria for updating and inserting rows.

Before deployment, data flows are packaged inside control flows, which dictate the sequence in which data flows run and define error handling, iterative processing, and other activities (see Figure 4). You can run mining flows, SQL scripts, and DB2 stored procedures; embed WebSphere DataStage ETL jobs that are run by a DataStage server; and invoke DB2 utilities such as REORG and RUNSTATS.

Control flows are deployed inside data warehouse applications on the WebSphere Application Server machine, making them visible to the Administration Console for scheduling. Role-based users can see and work with applications in different ways. Each control flow becomes a manageable process, and each process instance that you schedule is equivalent to a job (see Figure 5).

Who benefits from using SQW? Here’s a short list:

• Existing DB2 customers who build warehouses with hand-coded SQL and stored procedures
• New DB2 customers who have database skills and data sources that tend to be flat files and relational tables
• Customers building a layered warehouse architecture, with a conventional ETL system populating tables from external sources at the detail level
• Customers who want to automate tedious DBA tasks for warehouse maintenance.

SQW supports a full life cycle of warehouse building tasks, from physical data modeling through to the scheduling and monitoring of repeatable processes that maintain the performance and business access layers of the warehouse. Data mining and multidimensional cube objects, as well as any other in-database structures, can be reliably staged and maintained.

Integrated Data Mining

Data mining discovers patterns and unexpected insights that are hidden in large data sets. You can interactively create and visualize a mining model in the Design Studio to gain valuable insights about the data in your organization’s warehouse. You can generate SQL code to compute a mining model or to deploy the model’s related scoring function.

DWE 9.1’s Intelligent Miner component offers a framework for building and visualizing data mining models. These models can make use of several different data mining functions:

• Associations. What are the affinities in the data? Which products sell together? What combination of items does your supermarket basket contain?
• Sequences. What kinds of sequential patterns occur? What will customers buy next, given information about what they are buying now?
• Clustering. Which groups or segments appear in the data? What kinds of similarities exist? What are my customer profiles?
• Classification. How is the data classified and what categorical predictions can be made based on those classifications? Which types of customers are likely to default on a loan? What is the likely outcome of this treatment?
• Regression analysis. How is the data classified and what numeric predictions can be made? How much money will each customer spend this year? How likely is it, in numeric probability terms, that a customer will buy this item?

The integration of data modeling, SQL-based data flows, and mining flows in a common data warehouse project makes data mining in the Design Studio less of a technological challenge. Users who are familiar with data flows use the same drag-and-drop interface to build mining flows without writing code or learning complex statistical algorithms (see Figure 6).

For preprocessing steps, mining flows use many of the same operators as data flows, such as table sources, order by operators, and joins (see Figure 7).

The interactive flow editor provides stepwise execution, and data exploration features present an overview of the content and quality of your data. You can work from design through to the development of embedded applications that produce reports based on mining models.

OLAP Modeling

You can use Cube Views OLAP modeling features directly in the Design Studio to create metadata that describes your data in a multidimensional model or cube model. This metadata can also be used to create recommendations for materialized query tables, which contain aggregated data for query optimization.

In the Design Studio, the foundation for a cube model can be the same physical data model that you use to build data flows and mining flows. Based on the physical model, you can define all of the OLAP objects you need, including fact and dimension objects, measures, and hierarchies. You can also import and export OLAP metadata.

DB2 Alphablox

DB2 Alphablox is a custom development platform for BI applications that are visible to business executives and analysts through web pages and portals. You can create customized analytic components that are embedded into existing business processes and web applications. Business users depend on these portals and can take real actions based on the analysis of large volumes of warehouse data.

Developers can assemble applications by customizing modular, reusable Blox components that provide solutions for data access, user interfaces, HTML forms, business logic, and more. The DB2 Alphablox programming model consists of:

• An extensive set of JavaServer Pages (JSP) custom tag libraries
• Server-side APIs to Blox components based on JavaBeans technology
• Server-side access to the Blox user interface model
• JavaScript methods to invoke server-side APIs from web browsers.

Developers don’t need to know the technical details behind the Blox components — just the syntax and functionality that they provide. Developers can build a DB2 Alphablox cube based on Cube Views metadata and use the cube as the foundation for building and viewing custom reports. They can also generate dynamic, interactive reports from relational data sources — for example, an application that invokes a data mining model, extracts data from the model, and displays reports in a portal.

DB2 9 and DWE

A DWE 9.1.1 refresh available this quarter upgrades the DWE package to DB2 9 and addresses requests from early DWE 9.1 adopters. DB2 9 includes a number of critical enhancements in its role as the underlying engine for DWE, including self-tuning memory management, range partitioning, and row compression. To learn more about these features, search for “Software Announcement 206-128” on ibm.com.

Intelligence On Demand

DWE 9.1 removes complexity as a barrier to enterprise data warehousing. Measurable benefits include time to value, managed cost for warehouse-based analytics, and avoidance of multiple purchase justifications for BI tools. In this regard, DWE 9.1 is closely aligned with the IBM Information On Demand initiative, providing customers with reliable information that is readily available and easy to manage throughout the data warehousing life cycle. Customers can use this information to make fast, strategic business decisions and sustain success in the marketplace.

---

LISTING 1. SQL merge code generated from a data flow.

MERGE INTO DB2ADMIN.NEW_PRCHS_PROFILE
USING (
SELECT
STR_IP_ID AS STR_IP_ID,
PD_ID AS PD_ID,
TIME_ID AS TIME_ID,
NMBR_OF_MRKT_BSKTS AS NMBR_OF_MRKT_BSKTS,
NUMBER_OF_ITEMS AS NUMBER_OF_ITEMS,
PRDCT_BK_PRC_AMUNT AS PRDCT_BK_PRC_AMUNT,
CST_OF_GDS_SLD_CGS AS CST_OF_GDS_SLD_CGS,
SALES_AMOUNT AS SALES_AMOUNT
FROM
DB2ADMIN.STAGE_PRCHS_PROFILE INPUT_051) INPUT_01
ON (DB2ADMIN.NEW_PRCHS_PROFILE.STR_IP_ID = INPUT_01.STR_IP_ID)
WHEN MATCHED AND DB2ADMIN.NEW_PRCHS_PROFILE.SALES_AMOUNT > 10000 THEN
UPDATE SET
(STR_IP_ID, PD_ID, TIME_ID, NMBR_OF_MRKT_BSKTS, NUMBER_OF_ITEMS, PRDCT_BK_PRC_AMUNT, CST_OF_GDS_SLD_CGS, SALES_AMOUNT)
= (INPUT_01.STR_IP_ID, INPUT_01.PD_ID, INPUT_01.TIME_ID, INPUT_01.NMBR_OF_MRKT_BSKTS, INPUT_01.NUMBER_OF_ITEMS,
INPUT_01.PRDCT_BK_PRC_AMUNT, INPUT_01.CST_OF_GDS_SLD_CGS, INPUT_01.SALES_AMOUNT)
WHEN NOT MATCHED THEN
INSERT
(STR_IP_ID, PD_ID, TIME_ID, NMBR_OF_MRKT_BSKTS, NUMBER_OF_ITEMS, PRDCT_BK_PRC_AMUNT, CST_OF_GDS_SLD_CGS, SALES_AMOUNT)
VALUES (INPUT_01.STR_IP_ID, INPUT_01.PD_ID, INPUT_01.TIME_ID, INPUT_01.NMBR_OF_MRKT_BSKTS, INPUT_01.NUMBER_OF_ITEMS, INPUT_01.PRDCT_BK_PRC_AMUNT, INPUT_01.CST_OF_GDS_SLD_CGS, INPUT_01.SALES_AMOUNT)
ELSE IGNORE

---

The author thanks Qi Jin, senior manager of DB2 Data Warehouse Edition, for his technical assistance.

---

Bob Rumsby has worked as an editor, technical writer, course developer, and trainer in Silicon Valley for 16 years. He is currently an information development team lead in the User Technology group at IBM SVL in San Jose, Calif.

• 0 Comments

Use these tricks to generate IDS SQL scripts
By Lester Knutsen

All good DBAs have some tricks up their sleeves to make their jobs easier. One I like to use is to write an SQL query of the Informix system tables and use the output of that query to generate another SQL script. This trick saves a lot of time (and reduces the possibility of missing a table) when you’re working with a large database with many tables.

Let’s say you need to change all the tables in a database from page-level to row-level locking, or that you need to defer foreign key constraints on all the tables to do a load. You could write an SQL script to do this one table at a time, but with a few hundred tables this task quickly becomes lengthy and tedious. Or, you could write an SQL statement to query the system tables and generate the required SQL script for you. I’ll show you three examples for which this approach is particularly useful. First, when changing the locking mode of all tables in a database and, second, when changing database privileges. The third example shows how to generate a script to enable or disable all the constraints of all the tables in a database. This script can be very helpful when you need to load or change data en masse and constraints are preventing that from being accomplished.

Changing Page-Level Locking

When a table is first created, a locking mode is defined. The two lock modes are page level (all rows on a page are locked when one row is locked) and row level (only one row in use is locked). Page-level locking is the default for performance reasons. However, to maximize concurrence, a DBA will often need to change to row-level locking. You can create a script to first check the lock level of all tables in a database and then, for any tables that use page level locking, to generate the SQL code to alter that locking mode to row level. Listing 1 shows the SQL script that generates the second SQL script, which will change the lock mode of all tables to row-level locking. Listing 2 shows the output that results.

The basic process in all these scripts is to use the system tables combined with text strings in SQL to output a text file with SQL commands. You can create scripts to unload data, alter extent sizes, check tables’ grant privileges, and automate many other DBA tasks.

In Listing 1, the lines output to lockmod.sql without headings create a file with the SQL commands to execute what you need done, without the normal column headings that SQL produces. Note that you’ll use dbaccess to create the output, and dbaccess wraps the output file at 80 characters. Many SQL command lines will be longer then 80 characters, but this wrapping doesn’t cause a problem with the SQL generated — it just affects the way it looks.

Generating Scripts to Revoke Privileges

Another Informix default that I often need to change is public. When a table is created, public is granted select, update, delete, and insert privileges on that table by default. In other words, any user who can access the database can modify all data in that table. Listing 3 shows an SQL script that generates the SQL to revoke all public table privileges on a database.

Because it reads the system tables, this script can be run on any database. However, you can’t revoke privileges granted by someone else, so the script limits itself to tables on which you granted the public privilege. If several developers are creating tables in a database, each developer will need to run this script to revoke public privileges. The script can be modified to grant privileges to specific users or a set of users. Listing 4 shows the output that results from running this script.

Generate Scripts to Enable or Disable Constraints

On a large database with hundreds of tables, it would take a lot of typing to write out all the SQL commands to enable or disable constraints. Listing 5 is a little SQL script that may save you a bit of time and effort. This script will generate the SQL statements to enable or disable all the foreign key constraints in a database.

Script Away

I’ve shared just a few examples of the many scripts you can create using SQL with the system tables. On a large database with many tables, creating scripts by hand is tedious and error prone. Creating scripts using the system tables that store information about all the tables and columns in a database ensures that all tables are included. Check the software section of International Informix User Group Web site for more examples.

Listings

Listing 1. SQL script to generate another SQL script to set row level locking for all database tables.

{
####################################################
Author: Lester B. Knutsen email: lester@advancedatatools.com
Advanced DataTools Corporation
Description: Generate SQL to set row level locking for all database tables
####################################################
}
output to lockmod.sql — Create SQL script
without headings — Don’t include column headings
select “alter table “, — Text
trim(tabname) , — Table name
” lock mode (row);” — Text
from systables
where tabid > 99 — Don’t get the systables
and tabtype = “T” — Get real tables not views
and locklevel = “P” — Get tables with page level locking
order by tabname

Listing 2. lockmod.sql after the script has been run on the stores_demo database.

alter table call_type lock mode (row);
alter table catalog lock mode (row);
alter table cust_calls lock mode (row);
alter table customer lock mode (row);
alter table items lock mode (row);
alter table log_record lock mode (row);
alter table manufact lock mode (row);
alter table orders lock mode (row);
alter table state lock mode (row);
alter table stock lock mode (row);

Listing 3. SQL script to generate another SQL script to revoke all public privileges.

{
####################################################
Author: Lester B. Knutsen email: lester@advancedatatools.com
Advanced DataTools Corporation
Description: Revoke all public privileges for all tables
####################################################
}
output to “revokepub.sql”
without headings
select “revoke all on “, — Revoke all privileges
trim(tabname) , — Table name from systables
“from public;”
from systables , systabauth
where systables.tabid = systabauth.tabid
and grantee = “public” — Select table names where public has privileges
and grantor = USER — Only revoke the ones you have privilege to revoke
and systables.tabid > 99 — Don’t revoke the system table privileges

Listing 4. revokepub.sql after the script has been run on the stores7 database.

revoke all on customer from public;
revoke all on orders from public;
revoke all on manufact from public;
revoke all on stock from public;
revoke all on items from public;
revoke all on state from public;
revoke all on call_type from public;
revoke all on cust_calls from public;
revoke all on custview from public;
revoke all on log_record from public;
revoke all on catalog from public;

Listing 5. SQL script to generate SQL script to disable foreign key constraints.

{
####################################################
Author: Lester B. Knutsen email: lester@advancedatatools.com
Advanced DataTools Corporation
Description: Disable and enable all tables foreign key constraints
####################################################
}
output to ” disable_fk_constraints.sql” without headings
select “set constraints ” || trim(constrname) || “disabled ;”
from sysconstraints
where tabid > 99
and constrtype = “R”;
output to ” enable_fk_constraints.sql” without headings
select “set constraints ” || trim(constrname) || “enabled ;”
from sysconstraints
where tabid > 99
and constrtype = “R”;

Lester Knutsen
is president of Advanced DataTools Corp., an IBM Informix consulting
and training partner specializing in data warehouse development,
database design, performance tuning, and Informix training and support.
He is president of the Washington D.C. Area Informix User Group, a
founding member of the International Informix Users Group, and an IBM
gold consultant.

• 0 Comments

The focus method is handy when error checking forms. All it does is put the cursor in the given element, which although simple, can be quite handy for the user. Using focus is really simple:

<script type=‘text/javascript’>
function error_check()
{
if(document.myform.myfield.value == ‘’)
{
alert(“Please fill in all fields!”);
document.myform.myfield.focus();
return;
}
else
{
//sucess, what to do. . .
}
}</script>

Useful and simple, a very good combination. Focus is a pretty simple function, but it adds to the user experience, which is always good.

• 0 Comments

Note: In order to use this tutorial, you need to have the prototype javascript library.

My earlier post on Prototype talked about two of the basic functions, now we’re going to use those functions along with the built-in AJAX functions to make a simple little application. Our application is simply for dummy purposes, but will tell us who has birthdays in certain months. In our script, we will first have to include the Prototype and script.aculo.us libraries; Prototype must be called first!

We will be using the Ajax.Request function which takes a two paramaters, the URL of the handler, and the options. Options specify which method we will be using, the data we send, and what to do when the script completes. All the JavaScript coded by us is a mere 5 lines!

We’re going to setup our table first, which is really simple, just run this query on your database:

CREATE TABLE `birthdays` (`name` varchar(255) NOT NULL DEFAULT ‘’, `age` int(3) NOT NULL DEFAULT ‘0?,  `day` int(2) NOT NULL DEFAULT ‘0?, `month` int(2) NOT NULL DEFAULT ‘0?) ENGINE=InnoDB DEFAULT CHARSET=latin1

Next, we will code the AJAX handler script which receives a month number, then fetches all birthdays in that month:

mysql_connect(‘localhost’, ‘root’, ‘’);
mysql_select_db(‘tutorials’);
$query = mysql_query(“SELECT `name`, `age`, `day` FROM `birthdays` WHERE `month` = ‘{$_POST[’month’]}’”);
$out = “<ul>”;
if(mysql_num_rows($query) > 0)
{
while($r = mysql_fetch_row($query))
{
$out .= “<li>{$r[0]} turns {$r[1]} on the {$r[2]}</li>”;
}
}
else
{
echo “No birthdays in this month!”;
}
$out .= “</li>”;
echo $out;
?>

This is not complicated code, just a simple SELECT query and a while loop. Now, you may ask how we get the month number? That’s what Prototype sends us through post (you can use get, too). Whatever we echo, Prototype will get and store in the AJAX object as you will see shortly.

Our JavaScript contains one function! It starts and handles the request all at once.

function fetch_birthdays()
{
var month = $F(‘month’);
new Ajax.Request(‘ajax.php’, {method:‘post’, postBody:‘month=’ + month, onSuccess: function(t) { $(‘birthdays’).innerHTML = t.responseText; }});
}

We make use of our $F and $ functions; $F to get the current month from our select (which you’ll see later), and $ to update the div’s contents with the birthdays. Our AJAX request tells that the handler will be ajax.php, the method withh be post, and the data to send is month = whatever the month currently is. The onSuccess part may look weird to you. All we’re really doing is passing our AJAX object as a paramter (Prototype handles this), and then updating our div with the server’s response text, which will be an ordered list with birthdays!

The only part left is creating our form and update div, which is simple:

<h2>Birthdays</h2>
<p>Select a month from the drop down to see whose birthdays are in each month.</p>
<form name=‘months’>
<select id=‘month’>
<option value=‘1?>January</option>
<option value=‘2?>February</option>
<option value=‘3?>March</option>
<option value=‘4?>April</option>
<option value=‘5?>May</option>
<option value=‘6?>June</option>
<option value=‘7?>July</option>
<option value=‘8?>August</option>
<option value=‘9?>September</option>
<option value=‘10?>October</option>
<option value=‘11?>November</option>
<option value=‘12?>December</option>
</select>
<input type=‘button’ value=‘get birthdays’ onclick=“fetch_birthdays();” />
</form>
<div id=‘birthdays’></div>

Our button will call the fetch_birthday function which starts the AJAX request. . .

• 0 Comments

I recently found out about and downloaded a wonderful little package called script.aculo.us, for more information, check out my post on it. I’m going to assume that you have already downloaded script.aculo.us, which includes the Prototype JavaScript framework. The framework contains many functions, but we’re only going to cover two right now. These functions will be $ and $F (weird names, but you’ll find out why soon!)

The first function, $, will make your JavaScript development hundred’s of times faster. Honestly. Now, typing out document.getElementById(’id’) can get rather tiresome pretty quickly. Prototype’s $ function recgonizes this, it does the same thing as document.getElementById, but does it in one character! Here’s an example if you’re still a little confused:

var the_html = $(‘my_div’).innerHTML;

The next function is $F, it’s for forms. All you need to do is give one paramater, that is the input, select, radio button, etc name. $F will return the current value of that field! No more document.form_name.field_name.value, hoorah! Another example in case you’re still in the dark:

Enter your name: <input type=‘text’ id=‘my_name’ /> - <input type=‘button’ onclick=“alert(’Hi’ + $F(’my_name’) + ‘, I\’m Mike!);” value=’say hi!’ />

This is just the basics of Prototype, the rest of it is even cooler!

• 1 Comment

A common mistake made in JavaScript is the difference between names and IDs. In some browsers you are able to directly access an element by its ID such as:

document.id.(attribute)

However, that doesn’t work in all browsers and therefore should not be used. The correct way to access an element by its ID is with the function getElementById(), which is actually quite easy to remember if you think about it . This would be the way to use it:

document.getElementById(‘id’).(attribute)

Generally when the name attribute is used it’s for forms, and thus requires the name of the form to be called as well. The structure for this is:

document.formName.elementName.(attribute)

It’s a pretty simple concept, however it’s often misused, so make sure you know what you’re trying to do before you do it!

• 0 Comments

JavaScript has a handy, and fun, little thing called document.title, all it does is change the title, but it’s handy in certain situations. One of these situations is when dealing with AJAX, I was fetching a post, but the title remained the same. I quickly learned about document.title, and all my troubles were gone. All it takes is:
document.title = document.titleform.newtitle.value;
That would work if I had a form named titleform with a field called newtitle. To best implement this, it should be stored in a function, let’s say change():

function change(title)
{
document.title = title;
}

This code will change the title to whatever we want. *evil* I want it to update every keystroke, so we’re going to use the method onkeyup:

<form>
New Title: <input type=‘text’ onkeyup=‘change(this.value);’ />
</form>

Yea, I know, it’s really that simple. This is a special keyword, it means what you think, this! So it means this value, which is our new title. Heh, now, go off and entertain yourself with this.

• 0 Comments

Confirming with JavaScript is actually quite easy. You can just use a simple if/else statement with it and then have something happen in each situation.

What we’ll be using is the function confirm(). The confirm function is set as a variable initially then you can use the associated variable to check the outcome of the confirmation. On the user end they get a small alert window with your question asked in it and either an “OK” or “Cancel” option.

So, to begin we need to define our confirm pop-up. For this example we’ll ask the user if they want a cookie.

var wants_a_cookie = confirm(“Would you like a cookie?”);

That will put up the window, however there will be no consequence whether they press OK or Cancel. That’s where the if/else comes into play. The confirm() function when acknowledged will return either TRUE or FALSE. What we can do is just have a situation for each option. For this example I’ll just have an alert pop up telling them if they or if they don’t want a cookie.

if( wants_a_cookie ){
alert(“Hooray! You want a cookie!”);
}
else{
alert(“Oh no, you don’t want a cookie!”);
}

And that’s it! If you want the entire code to copy into your file it would look like this:

<script language=“JavaScript” type=“text/javascript”>
var wants_a_cookie = confirm(“Would you like a cookie?”);
if( wants_a_cookie ){
alert(“Hooray! You want a cookie!”);
}
else{
alert(“Oh no, you don’t want a cookie!”);
}
</script>

• 0 Comments

One would think that in order to get the current year with JavaScript, you just have to do date_obj.getYear. That’s logical, becuase to get the day it’s getDay, and month is getMonth, but JavaScript likes to be mean. (how rude!) Using getYear will return only a two digit number, for 2006, it’s 16, but we acutally want 2006. To get this, you need to use getFullYear.

I’ve always found this weird, but definitily something good to know so that when you try to get the year with JS and end up with some random number, you don’t pull your hair out.

• 0 Comments