Query/400 Modernization

This week, Dawn planned to blog about the Print_Query_Definition stored procedure as a great way to help you manage your hundreds or possibly thousands of Query/400 objects. Now I can't do the topic any kind of justice like she can, but she recommends a great article we just posted by Gene Cobb. 

"Sinking in a Query/400 Quagmire?" is a walkthrough of the database and query management problem and how this tool, introduced in V5R3, can help. It also provides some useful examples and offers up IBM Lab Services to help with your Query/400 modernization. 

Dawn will be back next month but she's taking a short break for personal reasons. 

—Tami Deedrick, managing editor, IBM Systems Magazine, Power Systems edition

 

 

IPCONFIG – A Secret Tool for IP Configuration

Have you ever wanted to be able to restrict the ephemeral port ranges configured on IBM i, clear the local DNS cache, or view the entries stored in the local DNS cache?

IPCONFIG is a tool that allows you to view or modify IP configuration settings that aren’t accessible through standard TCP/IP menus. This may be the first time you’ve heard of IPCONFIG because the existing settings are a mix of features that should never be changed, but a toggle was added "just in case." Now we’ve added some actual useful and interesting features into the mix. These new features provide the capability to work with ephemeral port ranges as well as the local DNS cache.

Ephemeral Port Options
The default ephemeral port range on the IBM i is 5000 through 65535 for both TCP and UDP. This broad range may allow applications using ephemeral ports to bind to a port used by a server application causing the application to fail on startup because the server’s port is already in use. IPCONFIG’s new ephemeral port options provide a solution to this problem. The ephemeral port range for both TCP and UDP can now be uniquely configured.

These options set the lowest and highest valid ephemeral port to be used by TCP or UDP.  The <ephemeralPort> value must be in the range of 5000 to 65535. The ephemeral port ranges can be modified at anytime; however, the newly configured values will not apply until after TCP has been restarted.

TCP Ephemeral Port Range Options
-tcp_ephemeral_low:<ephemeralPort>
-tcp_ephemeral_high:<ephemeralPort>
UDP Ephemeral Port Range Options
-udp_ephemeral_low:<ephemeralPort>
-udp_ephemeral_high:<ephemeralPort>

Displaying Ephemeral Port Ranges
-display

The -display option displays the current configuration values for all the IPCONFIG options. The TCP and UDP ephemeral port ranges can be viewed using this option. Shown in the green screen below, the configured (and active) TCP ephemeral port range is 20000 through 65535. The UDP ephemeral port range is configured to 25000 through 55000; however, this value will not apply until TCP/IP has been restarted.  The active UDP ephemeral port range that applies until TCP/IP is restarted is 20000 through 65535.

DNS Cache Option
IPCONFIG provides a new DNS option with two sub options: display and reset. These functions allow you to work with the DNS cache.

Display
-dnsCache:display <-t, -d>

This sub option displays a list of the DNS cache entries. The optional parameters -t and -d offer further display choices. By default, the cache entries are output in a list format. Using the -t parameter, the cache entries can be printed in a table format that’s simple to search and use to compare particular cache entries. In addition to displaying the cache entries, the –d parameter will create an informational local sockets LIC log (major code 2C00; minor code 9900) containing the command output. The LIC log can be used with both list and table formats. A LIC log can be dumped to a spooled file for further analysis.
Each cache entry contains the query sent to the DNS server and the response received. The entry will also include the original time to live and the remaining time to live in the cache as indicated by the DNS server.

List format is shown below.

Table format is shown below.

Reset
-dnsCache:reset
The reset sub option will clear all of the entries stored in the DNS cache.

Using IPCONFIG
The following PTFs provide the support for these IPCONFIG enhancements:
7.1    – MF52889
6.1.1 – MF52891
6.1    – MF52869

You use the System Service Tools (STRSST) Display/Alter/Dump function to invoke the IPCONFIG Advanced Analysis feature. You must be authorized to use System Service Tools and you also must be authorized to use the Display/Alter/Dump service function.

To change the settings with the STRSST command, use the following steps:

1.    Open a character-based interface.
2.    On the command line, type STRSST.
3.    Type your service tools user name and password.
4.    Select option 1 (Start a service tool).
5.    Select option 4 (Display/Alter/Dump).
6.    Select option 1 (Display/Alter storage).
7.    Select option 2 (Licensed Internal Code (LIC) data).
8.    Select option 14 (Advanced Analysis).
9.    Select option 1 (IPCONFIG).


I'd like to thank Ashley Good, Lindsay Reiser, and Tim Mullenbach for writing this blog article.  Ashley, Lindsay, and Tim all work on the TCP/IP Networking team in the IBM i development lab.

Top Ten Most Commonly Used IBM i Passwords

What are your IBM i passwords?
Qsecofr?  qsec0fr?  qsys0pr?  11111111?  22222222?  qibm?  Or maybe ibmce?

Like most systems, the IBM i passwords tend to contain or be the user profile, or one of those passwords found in a list of commonly used passwords published regularly by many of the white hat organizations.

The Lab Services Security team often performs security assessments;  in fact, it's our most requested security service. Invariably during these security assessments we find too many privileged profiles and default passwords. 

Compounding the password problem is the misconception that because the i is considered to be one of the most secure operating systems in the industry, it's inherently secure without any need for administration. As a result, many IBM i customers have given little thought to passwords or password complexity unless forced upon by an auditor, at least until quite recently.

In a recent study, it was reported that 7 percent of the most commonly used passwords could be found in a dictionary of 100 words. The study found that the shortness and simplicity of passwords means many users select credentials that will make them susceptible to basic forms of cyber attacks known as "brute-force attacks." Nearly 50 percent of users used names, slang words, dictionary words or trivial passwords (consecutive digits, adjacent keyboard keys, etc.). Other studies have shown as many as 36 percent of the most commonly used passwords could be found in a list of 5,000 words. This means that a dictionary style, brute-force attack, could be effective in as much as 36 percent of password-cracking attempts.

The password policy capabilities for IBM i are strong as demonstrated below, never the less despite the proven security features on IBM i, a poor selection of a password can make it vulnerable to a brute-force attack

The following outlines the IBM i password policy capabilities for version 5 release 4:

• Minimum password length
• Maximum password length
• Restricting new passwords that are the same as old passwords
• Restricting use of consecutive digits
• Requiring a numeric character
• Limiting consecutive repeated alphanumeric characters
• Requiring different characters in the same position of a new password

The additional IBM i password policy capabilities for version 6.1 are:

• Specifying minimum and maximum number of special characters, alphabetic characters, or numeric characters
• Requiring mixed case
• Requiring or disallowing the first character or last character of a password from being a special character or numeric character or alphabetic character
• Disallowing profile name within the password
• Requiring three out of four password components: uppercase characters, lowercase characters, special characters, and numeric characters

Brute force is basically a term for a method of “forcing” combinations of passwords against an account login and is usually performed by a software program to automate the process. Sometimes these programs use a dictionary file of common words to guess the password, or they will literally try every combination of letters, numbers and characters possible. The possibility of guessing the password with a dictionary file is extremely high, especially when the attack is automated. Despite all of the warnings of security breaches, one out of five users still decides to leave the digital equivalent of a key under the doormat: they choose a simple, easily guessed password like “abc123,” “iloveyou” or even “password” to protect their identity.

Now, let’s answer the question: "Why do I need a Password Validation Tool? I thought the built in security features of IBM i already offered enough security and password support?"

At the request of members of the IBM i Large Users Group (LUG), IBM Systems Lab Services and Training developed the “Password Validation Tool” to further enhance the built-in security features of IBM i by preventing users from using passwords that aren't audit compliant. The intention is to assist users and security managers in tightening up the password security on their IBM i systems by preventing key user mistakes. Overall, this tool supports with security compliance and auditability, reduces administrative costs associated with password reviews, and provides greater flexibility in enforcing strict password policy statements.

Key features of the Password Validation Tool are:

• Validates upon entry that an employee's password meets company and industry recommended security rules and guidelines.
• Allows users to define restricted words from 3 to 128 characters and checks to insure the password does not contain predefined words. 
• Allows the security administrator to establish and provide a dictionary of excluded terms, to further tighten up password security. This is an enhancement to IBM i built in security which does not specifically allow you to exclude certain terms that may be deemed offensive, or too risky.
• Allows for additional custom checks that are not covered by the password policy capabilities of IBM i as additional function can be tailored to a company’s password policy

This tool is an affordable addition to your system's security tools. For more information regarding this tool, please contact IBM Systems Lab Services and Training at [email protected], or visit: ibm.com/systems/services/labservices

This blog article was compiled by Leonard Broich, Sharon Su, Terry Ford, and Vincent Hennessey, who all work for IBM Systems Lab Services and Training in Rochester, Minn. IBM Systems Lab Services and Training is composed of experts who develop and deploy solutions across IBM's systems family offerings. Services and offerings include in-depth product expertise, knowledge transfer and platform specific hardware and software solutions.

i Can ... Find SQL Server Mode Jobs

This week's blog post was written by Scott Forstie. Scott is a senior software engineer at IBM, and he's the SQL development leader for DB2 for IBM i in Rochester, Minn. Before working on DB2, he worked on UNIX enablement for the AS/400 and S/390 systems. Thanks, Scott!

Hello again IBM i users. This blog thread explains a recent DB2 for i enhancement to IBM i on V5R4, 6.1 and 7.1. Anyone responsible for administering, tuning or explaining the SQL Server Mode (e.g. QSQSRVR jobs) activity might find the QSYS2.FIND_QSQSRVR_JOBS() procedure a useful tool. This procedure has been added to QSYS2 after application of PTFs (see the Service Information section for details). The procedure is passed a single parameter, the qualified job name of an application job. If the target job is active and is set up to use SQL Server Mode, the procedure determines which QSQSRVR jobs are being used by the application, in the form of active SQL Server Mode connections. The procedure collects and returns work management, performance and SQL information and returns two SQL result sets: (1) Summary information and (2) Detailed SQL Server Mode job information.

How is this procedure useful? When you have an important application instance (job) that uses QSQSRVR jobs, it can be quite difficult to determine the "total system impact" of the application. How many SQL Server Mode jobs are in use at that moment? Is this application responsible for a QSQSRVR job that is consuming a lot of CPU or holding onto object locks? The FIND_QSQSRVR_JOBS() procedure provides some of these answers by tying together the application and its SQL Server Mode job use.

Example Invocation:

call QSYS2.FIND_QSQSRVR_JOBS('566463/EBERHARD/QP0ZSPWP ');

Procedure definition:

CREATE PROCEDURE QSYS2.FIND_QSQSRVR_JOBS( JOB_NAME VARCHAR(28) )

    NOT DETERMINISTIC

    MODIFIES SQL DATA

    CALLED ON NULL INPUT

    DYNAMIC RESULT SETS 2

    SPECIFIC FINDSRVR

    EXTERNAL NAME 'QSYS/QSQSSUDF(FINDSRVR)'

    LANGUAGE C PARAMETER STYLE SQL;

Authorization:

On IBM i 6.1, to invoke QSYS2.FIND_QSQSRVR_JOBS the user needs *JOBCTL special authority.

On IBM i 7.1, to invoke QSYS2.FIND_QSQSRVR_JOBS the user needs *JOBCTL special authority, QIBM_DB_SQLADM Function usage or QIBM_DB_SYSMON Function usage.

> call QSYS2.FIND_QSQSRVR_JOBS('650261/SCOTTF/QP0ZSPWP')

SQL State: 38501

Vendor Code: -443

Message: [CPF43A4] *JOBCTL special authority, QIBM_DB_SQLADM or QIBM_DB_SYSMON Function usage is required. Cause . . . . . : The user profile is required to have *JOBCTL special authority or be authorized to either the QIBM_DB_SQLADM or QIBM_DB_SYSMON Function through Application Administration in System i Navigator. The Change Function Usage (CHGFCNUSG) command can also be used to allow or deny use of the function.

For example: CHGFCNUSG FCNID(QIBM_DB_SQLADM) USER(xxxxx) USAGE(*ALLOWED).

Recovery . . . : Have the security officer grant *JOBCTL special authority or add the QIBM_DB_SQLADM Function usage or add the QIBM_DB_SYSMON Function usage.

Usage:

The procedure can be called from any environment. The input parameter is the application qualified job name. When called from within System i Navigator's Run SQL Scripts, two results sets are displayed. When called from STRSQL or elsewhere, the user needs to query the temporary tables to see the data.

select * from qtemp.QSQSRVR_DETAIL order by TOTALCPU desc;

select * from qtemp.QSQSRVR_SUMMARY;

Use this query to see the summary information in the same form that is returned within the result set.

SELECT SERVER_MODE_JOB,count(*) AS "QSQSRVR JOB COUNT", SERVER_MODE_CONNECTING_JOB, SUM(TOTAL_PROCESSING_TIME) AS "CPU USED (MILLISECONDS)", SUM(TEMP_MEG_STORAGE) AS "TEMP STORAGE USED (MB)", SUM(PAGE_FAULTS) AS "PAGE FAULTS", SUM(IO_REQUESTS) AS "I/O REQUESTS" from SESSION.QSQSRVR_SUMMARY GROUP BY GROUPING SETS (SERVER_MODE_JOB , SERVER_MODE_CONNECTING_JOB) ORDER BY 1;

Example output:

Result set 1: Summary information

 

Result set 2: Detailed information

Result set definition:

Result set 1: Summary information

QSQSRVR_SUMMARY (

    SQL_IDENTITY FOR COLUMN SQL_I00001 INTEGER NOT NULL ,

    NUMBER_OF_ACTIVE_JOBS FOR COLUMN NUMJOBS INTEGER NOT NULL ,

    SERVER_MODE_JOB FOR COLUMN SRVRJOB CHAR(28) CCSID 37 NOT NULL ,

    SERVER_MODE_CONNECTING_JOB FOR COLUMN CONNJOB CHAR(28) CCSID 37

NOT NULL ,

    TOTAL_PROCESSING_TIME FOR COLUMN TOTALCPU BIGINT NOT NULL ,

    TEMP_MEG_STORAGE FOR COLUMN TEMPMSTG INTEGER NOT NULL ,

    PAGE_FAULTS FOR COLUMN FAULTS BIGINT NOT NULL ,

    IO_REQUESTS FOR COLUMN IOREQS BIGINT NOT NULL )

 

Result set 2: Detailed information

QSQSRVR_DETAIL (

    SQL_IDENTITY FOR COLUMN SQL_I00001 INTEGER NOT NULL ,

    JOB_NAME FOR COLUMN JOBNAME CHAR(10) CCSID 37 NOT NULL ,

    USER_NAME FOR COLUMN USERNAME CHAR(10) CCSID 37 NOT NULL ,

    JOB_NUMBER FOR COLUMN JOBNUM CHAR(6) CCSID 37 NOT NULL ,

    JOB_INTERNAL_IDENTIFIER FOR COLUMN JOBID CHAR(16) CCSID 37 NOT NULL ,

    CURRENT_USERNAME FOR COLUMN CURRUSER CHAR(10) CCSID 37 NOT NULL ,

    SUBSYSTEM_DESCRIPTION_NAME FOR COLUMN SBSNAME CHAR(10) CCSID 37 NOT NULL ,

    RUN_PRIORITY FOR COLUMN PRIORITY INTEGER NOT NULL ,

    SYSTEM_POOL_IDENTIFIER FOR COLUMN POOLID INTEGER NOT NULL ,

    TOTAL_PROCESSING_TIME FOR COLUMN TOTALCPU BIGINT NOT NULL ,

    PAGE_FAULTS FOR COLUMN FAULTS BIGINT NOT NULL ,

    IO_REQUESTS FOR COLUMN IOREQS BIGINT NOT NULL ,

    MEMORY_POOL_NAME FOR COLUMN POOLNAME CHAR(10) CCSID 37 NOT NULL ,

    TEMP_MEG_STORAGE FOR COLUMN TEMPMSTG INTEGER NOT NULL ,

    TIME_SLICE FOR COLUMN TSLICE INTEGER NOT NULL ,

    DEFAULT_WAIT FOR COLUMN DFTWAIT INTEGER NOT NULL ,

    SQL_APPLICATION_LIBRARY FOR COLUMN SQLLIB CHAR(10) CCSID 37 NOTNULL ,

    SQL_APPLICATION_PROGRAM FOR COLUMN SQLPGM CHAR(10) CCSID 37 NOT NULL ,

    SQL_APPLICATION_TYPE FOR COLUMN APPTYPE CHAR(10) CCSID 37 NOT NULL ,

    SERVER_MODE_CONNECTING_JOB FOR COLUMN CONNJOB CHAR(28) CCSID 37

NOT NULL ,

    SERVER_MODE_CONNECTED_THREAD FOR COLUMN CONNTHD CHAR(10) CCSID 37

NOT NULL ,

    STATUS_OF_CURRENT_SQL_STMT FOR COLUMN STMTSTAT CHAR(10) CCSID 37

NOT NULL ,

    SQL_STATEMENT FOR COLUMN SQLSTMT VARCHAR(1000) CCSID 37 NOT NULL )

Service Information:

V5R4 PTFs:

PTF '5722SS1 V5R4M0 SI40098'

PTF '5722SS1 V5R4M0 SI40084'

PTF '5722SS1 V5R4M0 SI40083'

 

6.1 PTFs:

PTF '5761SS1 V6R1M0 SI40100'

PTF '5761SS1 V6R1M0 SI40099'

PTF '5761SS1 V6R1M0 SI40070'

PTF '5761SS1 V6R1M0 SI40068'

 

7.1 PTFs:

PTF '5770SS1 V7R1M0 SI40101'

PTF '5770SS1 V7R1M0 SI40124'

PTF '5770SS1 V7R1M0 SI40125'

i Can ... Search XML in OmniFind V1R2

Nick Lawrence wrote this week's blog as a followup to his recent "i Can … Find a Needle in a Haystack using OmniFind Text Search Server for DB2 for i." Nick works for IBM in Rochester, Minn., on DB2 for IBM i. His responsibilities include catalog support, DB2 object management and full-text search. Thanks, Nick!

Now that the V7R1 XML enhancements for DB2 for i have been announced, it’s a great time to mention the XML search support that is available in OmniFind V1R2. 

When the text search index is built over an XML column, OmniFind will, by default, include the XML markup when indexing and searching documents. XML search allows a search to be scoped to a specific element or attribute, rather than the entire document. 

In addition, the search syntax allows comparing an element or attribute value to a numeric, ISO date or ISO dateTime value during the search.

For example, let’s say I insert the document below into an XML column PRODUCT_ORDER of a table ORDERS:

> INSERT INTO ORDERS (PRODUCT_ORDER) VALUES(

'<ORDER>

<CUSTOMER>

<NAME> George Jetson </NAME>

       <TITLE> digital index operator </TITLE>

</CUSTOMER>

<PRODUCT_NAME> Referential Universal Digital Indexer (RUDI) </PRODUCT_NAME>

<COST unit="space tokens">  999.99  </COST>

<TIME_ORDERED> 2010-04-20T13:00:00 </TIME_ORDERED>

<DATE_DELIVERED> 2010-04-22 </DATE_DELIVERED>

</ORDER>');

Now after my XML data has been indexed using OmniFind, I can perform searches that include the XML markup in the search.

Maybe I want to search for orders where the customer’s title is a variation of “digital indexing operator”.  (Notice that “indexing” is a different linguistic variation of the word “index” that exists in the /ORDER/CUSTOMER/TITLE element of my document that I hope to find.)

I can find my document with this query:

> SELECT PRODUCT_ORDER FROM ORDERS WHERE CONTAINS(PRODUCT_ORDER, '@xmlxp:''/ORDER/CUSTOMER/TITLE[. contains("digital indexing operator")] '' ' ) = 1;

Those familiar with the popular W3 XPath syntax will notice the XML search syntax used by OmniFind is similar to that standard. 

The capability to include numeric, xs:date and xs:dateTime values in the search adds more potential.

Let’s say I only want rows for orders from George that were ordered this year.

  > SELECT PRODUCT_ORDER FROM ORDERS WHERE CONTAINS(PRODUCT_ORDER, '@xmlxp:''/ORDER[CUSTOMER/NAME contains("George Jetson") and TIME_ORDERED >= xs:dateTime("2010-01-01T00:00:00")]'' ') = 1;

The same concept applies to the xs:date data type. I can search for orders delivered to George on or before 2010-12-31.

>   SELECT PRODUCT_ORDER FROM ORDERS WHERE CONTAINS(PRODUCT_ORDER, '@xmlxp:''/ORDER[CUSTOMER/NAME contains("George Jetson") and DATE_DELIVERED <= xs:date("2010-12-31")]'' ') = 1;

OmniFind is optimized for searches that involve text, but there’s no requirement that the search include a full text search ‘contains’ in the xmlxp expression.

>  SELECT PRODUCT_ORDER FROM ORDERS WHERE CONTAINS(product_order, '@xmlxp:''/ORDER/COST[.  > 999] '' ') = 1;

Returns all rows for orders where cost is greater than 999.

Searches can include attributes in addition to elements:

>  SELECT PRODUCT_ORDER FROM ORDERS WHERE CONTAINS(PRODUCT_ORDER, '@xmlxp:''/ORDER/COST[.  > 999 and @unit = "space tokens"] '' ') = 1;

Returns all rows for orders where the cost is greater than 999 and the “unit” attribute of COST is “space tokens”.

For the advanced XML user, the XML search syntax also supports XML namespaces on element and tag names.

In addition to XML support, there are a number of other improvements that were added in the new release. 

•  A text Search index’s structure can be backed up and restored with traditional IBM i Save and Restore interfaces.
•  Move and Rename of a text search index and it’s based on table are now supported.
• The Generate SQL Data Definition Language (QSQGNDDL) API is supported for text search indexes
• IBM System Director Navigator for i supports many common administrative tasks for Text Search indexes.
• The text search server can have a unique alias name assigned to it, in addition to the numeric identifier assigned to it by the system.

These are some exciting capabilities for search and XML, in this new release…worth keeping an eye on.

i Can … Find a Needle in a Haystack using OmniFind Text Search Server for DB2 for i

Almost every application or Web domain that we come in contact with seems to have a search engine associated with it. Whether we’re looking for information about the latest gadget, answers to one of the great mysteries of the universe, or that lost e-mail, search engines are indispensable tools for finding relevant information in a vast sea of data. Did you know that starting in 6.1 this search technology is available for DB2 for i?

OmniFind Text Search Server for DB2 for IBM i is a no-additional-charge licensed program that, once installed, enables indexing and searching text data stored in a DB2 column. Full-text search indexes can be built over columns of many different data types that can contain text (BLOB, CLOB, DBCLOB, CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BINARY and VARBINARY).

 In addition to plain text, the column may contain HTML, XML, or rich-text documents such as PDF, Microsoft Word, Lotus WordPro or Microsoft PowerPoint.

For example, let’s assume I have a set of short stories (in PDF format) that I want to store in a DB2 BLOB column.

I’ve created a table to store the documents, using SQL, like this:

> CREATE TABLE story_library.story_table(
story_id         INTEGER GENERATED ALWAYS AS IDENTITY
NOT NULL,
            publish_date          DATE,
            story_doc         BLOB(10M),
            PRIMARY KEY (story_id));


Now, let’s assume I’ve loaded the above table with all of my great stories, and I’ve used OmniFind to index the text data in the story_doc column.

(Creating and administrating an index is accomplished through the use of SQL-stored procedures and is explained in detail in the product’s user manual.)

The text search index allows me to use the built-in CONTAINS and SCORE functions in my SQL queries. CONTAINS will tell me if a document matches my query. A zero return code indicates that the document isn’t a match, while a one indicates that the document is a match. The SCORE function will return a score value that indicates how relevant the match is.

If I wanted to retrieve the story_id for stories about a blind mouse, then I could write an SQL query like this:

> SELECT story_id FROM story_library.story_table WHERE CONTAINS(story_doc, 'blind mouse') = 1;

This query result will include matches that we’d expect from a typical search engine.  The search is case insensitive, and linguistic variations on the search words will be matched. In other words, the previous query will indicate a match for documents that contain “Blind Mice.” In a similar manner, a search for “bad wolves” would return documents that contained “the Big Bad Wolf.”

Other common conventions for search syntax are honored. If I really only want exact matches for “three blind mice,” with the search terms in order and no variations on the words considered, I can enclose the search expression in double quotes.

    SELECT story_id FROM story_library.story_table WHERE CONTAINS(story_doc, '"three blind mice"') = 1;

The logical operators AND, OR, and NOT are supported,
    SELECT story_id FROM story_library.story_table WHERE CONTAINS(story_doc, '"three little pigs" AND “big bad wolf”') = 1;

Usually when we use a search engine, we want the most relevant document to be listed first in the results. This is the purpose of the SCORE built-in function. SCORE returns a value between zero and one.  Zero indicates the document isn’t a match at all, and one indicates a theoretical perfect match of the search criteria.

Because the score indicates “how well” a document matches a search query compared to other documents in the index, it can be used to order the documents returned in a result set.

i.e.,
    SELECT story_id FROM story_library.story_table WHERE CONTAINS(story_doc, '"three little pigs" AND “big bad wolf”') = 1 ORDER BY SCORE(story_doc, '"three little pigs" AND “big bad wolf”') DESC;

The addition of “ORDER BY SCORE” to the SQL query ensures that the most relevant documents are returned first.

Let’s say I only want stories about little pigs that were published in 2010. This proves to be very easy to do using SQL and CONTAINS.

> SELECT story_id FROM story_library.story_table WHERE CONTAINS(story_doc, 'little pigs') = 1 AND YEAR(publish_date) = 2010;

In this blog, we’ve seen the new 6.1 built-in CONTAINS and SCORE functions allow an application to locate and rank unstructured text documents by using powerful (yet still familiar) search expressions. When working with text data in a DB2 column, full-text indexing is an invaluable way of efficiently figuring out which documents are of the most interest and importance.

For further reading on how to create and administer a text search index, as well as more sophisticated search examples, check out the OmniFind documentation in the IBM i Information Center.

Another great resource is Kent Milligan’s white paper.

This week's blog article was written by Nick Lawrence. Nick works for IBM in Rochester, Minn., on DB2 for IBM i. His responsibilities include catalog support, DB2 object management and full-text search. Thanks Nick!

i Can … Excel at SAP Benchmarking With IBM i 6.1 and POWER7

Ron Schmerbauch wrote this week’s blog on SAP. Ron is a member of the IBM i SAP Development Support team in the Rochester (Minn.) development lab.

With the announcement of POWER7 came another record setting result on IBM i for the SAP Business Intelligence Mixed Load (BI-MXL) Standard Application Benchmark. The key figure of this benchmark is the number of query navigation steps/hour, a measurement of overall throughput. In October 2009, we were able to run 154,447 query navigation steps/hour on eight POWER6 processor cores and IBM i 6.1. Our latest result was achieved with the POWER7 processor-based Power 750 Express where we hit 241,526 query navigation steps/hour using eight cores. That’s a 56-percent improvement from POWER6 to POWER7 with the same number of cores!

The POWER7 supports SMT4 (four threads of execution within each core), where the POWER6 supported only SMT2 (two threads within each core). This is important for application server architectures like SAP’s where many users on PC clients are sharing a set of application server jobs on IBM i. Our results show that more users can simultaneously get work through the system by using POWER7.

Although POWER7 offers a benefit over older hardware, the star of the SAP BI benchmark is DB2 for IBM i. When the new DB2 for IBM i SQL Query Engine (SQE) was being developed several years ago, SAP BI experts visited Rochester to discuss and review the designs because SAP’s BI application was heavily using the type of complex queries that SQE was hoping to excel with. Since the introduction of SQE, IBM i has been publishing leading SAP Business Warehouse (BW) and BI benchmarks.

The current and most challenging workload of all has been BI-MXL.SAP designed this benchmark specifically to defeat the high degree of tuning measures that are usually implemented in benchmarking performance environments. In older SAP BI workloads, the dataset was static and extreme tuning could be applied to get fast responses. But BI-MXL requires that both queries and data upload/updates are executed simultaneously. This makes the data that’s being queried in the system dynamic, just like in a real customer environment, and the DB2 for i engine has to keep coming up with the right answers and it has to do it quickly.

Although the SAP application server engine uses the same core set of source code for all SAP platforms, the SAP BI application layer implementation is allowed by SAP to take advantage of DB2 for i specific features. For SAP BI on i, this means SAP supports the use of DB2 on i features like Encoded Vector Indexes (EVIs), Materialized Query Tables (MQTs), Lookahead Predicate Generation (LPG) optimization and partitioned table support. EVIs are included in SAP’s automatic indexing strategy for DB2 on i, and LPG is used by default within the optimizer. MQTs and partitioned tables are features that SAP can recognize within the application if you desire to use them.

The i and AIX operating systems share a common code base, compiler and C/C++ runtime for the SAP application server engine. Additional IBM Power Systems leadership results with POWER7 on the SAP Sales and Distribution (SD) Standard Application benchmark showed a high degree of scalability in the Power 750 family with up to 32 cores. Because of the commonality in the core SAP application server engine executables, i and AIX are able to share a common sizing matrix to determine which Power System model would be suitable for a given SAP workload requirement.

Look for more SAP BI-MXL publishes on IBM i with POWER7 in the coming months – with even better results on the next version of the IBM i OS.

 

 

i Can…Use RPG and SQL Together

Gina Whitney wrote this week’s blog. Gina is a member of the SQL development team for DB2 for IBM i in Rochester, Minn.  She’s responsible for the SQL precompilers. Thanks, Gina!

ILE RPG and SQL are both powerful and useful languages. I’m sure many of you use one or both of these languages, but did you know that they can be used together? By using the SQL precompiler—the Create Structured Query Language ILE RPG Object (CRTSQLRPGI) command—you can embed SQL statements right in your RPG source. You can also call your RPG source from an SQL statement by creating SQL external functions and procedures. This blog will focus on embedding SQL statements in your RPG source.

SQL statements can be written in both fixed and free format RPG. When you compile your source, you use the CRTSQLRPGI command. The precompiler finds the SQL statements and converts them to RPG source code. The precompiler generates a new source member, which is used as input to the RPG compiler.

In fixed format, the SQL statement starts with EXEC SQL and ends with END-EXEC.

      C/EXEC SQL UPDATE DEPARTMENT

      C+ SET MGRNO = :MGR_NUM

      C+ WHERE DEPTNO = :INT_DEP

      C/END-EXEC

In free format, the SQL statement starts with EXEC SQL and ends with a semicolon.

      EXEC SQL UPDATE DEPARTMENT SET MGRNO = :MGR_NUM

      WHERE DEPTNO = :INT_DEP;

The free format support was added in IBM i 5.3. Since the same precompiler gets called no matter what value is specified on the target release parameter (TGTRLS), SQL statements in free format will be allowed with any valid value specified on the TGTRLS parameter.

RPG has the control-specification (H-Spec) that tells the compiler how to deal with devices and how certain types of information will be represented, as well as including keywords that will override parameters specified on the command. The SQL precompiler has a similar feature—the SQL SET OPTION statement.

      EXEC SQL SET OPTION

      NAMING = *SQL,                          

      CLOSQLCSR = *ENDMOD,                    

      COMMIT = *NONE,          

      DATFMT = *ISO,

      COMPILEOPT = 'DBGVIEW(*LIST)';             

The SET OPTION statement must be the first SQL statement in the source. I really like this statement since it lets me not have to remember to specify certain parameters on the CRTSQLRPGI command. But the coolest part of the statement is that you can specify compiler parameters that aren’t allowed in the H spec. Yeah, you read that right. You just need to specify the parameters in the COMPILEOPT string. This string is added to the compiler command that’s built by the precompiler. The only thing you need to remember is to not use any of the parameters that the precompiler already passes to the compiler. This will cause the compiler command to fail because of duplicate parameters. There’s also a compiler options (COMPILEOPT) parameter on the CRTSQLRPGI command. COMPILEOPT was added in IBM i 5.3 and is allowed to be specified with any valid value specified on the TGTRLS parameter.

Speaking of parameters, the RPG preprocessor options (RPGPPOPT) parameter on the CRTSQLRPGI command is one that you really want to know about. The precompiler only handles the /COPY directive and is restricted to only one level, so you can’t have nested /COPY. Since the other directives are not handled, the precompiler scans the source that’s in both the /IF and /ELSE. Specifying *LVL1 or *LVL2 on the RPGPPOPT parameter will tell the precompiler to call the compiler to have it preprocess the source. The compiler will handle all the directives and generate a new source member for the precompiler to use as input. The only difference between *LVL1 and *LVL2 is that *LVL1 doesn’t expand the /INCLUDE directive. The /INCLUDE directive was originally created as a way to hide code from the precompiler. The RPGPPOPT parameter is valid on IBM i 5.3, but not for previous releases.

The precompiler now has variable scoping. This enhancement is in the base of IBM i 6.1 and PTFed in IBM i 5.4 (product 5722SS1: SI34160 and SI34161 and product 5722ST1: SI34384 or database group PTF SF99504, #20). Before this support was added, all variables in the entire source needed to be unique to be used by SQL. Now the precompiler recognizes procedures and scopes the variables to the procedure they’re defined in.

In IBM i 6.1, the precompiler added the support to take an IFS source stream file as input. The source stream file (SRCSTMF) parameter is used to specify the path name of the IFS source stream file to be compiled. The SQL include directory (INCDIR) parameter is used for the SQL include statement. If you want to have an INCDIR parameter on the compiler command, you’ll need to use the COMPILEOPT parameter. Another thing to remember when your source is in IFS is that the precompiler won’t expand /COPY statements. If you have SQL statements in your /COPY files, you’ll need to specify a value other than *NONE on the RPGPPOPT parameter. One benefit of having your source in IFS is that it gives you more flexibility with your source code management than the traditional file system.

Other IBM i 6.1 enhancements to the SQL precompiler include: the capability to do a LIKE on the SQLCA variables, increased LOB variable limits and the new file enhancements (EXTNAME enhancement, LIKEFILE, EXTDESC and QUALIFIED).

Using the SQL precompiler lets you bring two powerful languages together. As you can see, there have been a lot of enhancements to make it even easier. Here’s a quick review:

Enhancement	Release	Can TGTRLS(*PRV) be specified?
SQL in free format	5.3	Yes
COMPILEOPT	5.3	Yes
RPGPPOPT	5.3	No
Variable scoping	5.4	Yes
Source in IFS	6.1	Yes
LIKE on the SQLCA variables	6.1	Yes
Increased LOB variable limit	6.1	No
New file enhancements	6.1	No

Two great places to go for help with embedding SQL in RPG are the RPG Café and the RPG400-L mailing list. The RPG Cafe is the place for RPG developers and IBM developers to mingle and discuss RPG. Subscribing to RPG400-L lets you use the list to e-mail other RPG developers. The e-mails are archived so you have the capability to search past posts.

There is also a precompiler manual, Embedded SQL programming, in the IBM i information center. 

 

 

 

i Can … Cancel SQL Statements

A new DB2 for IBM i supplied procedure is included with Database Group PTF for IBM i 6.1 (Level 12). The procedure, QSYS2.CANCEL_SQL(), can be called to request the cancellation of an SQL statement for a target job.

SQL cancel support provides an alternative to end job immediate, when deciding how to deal with an orphaned or runaway process. End job immediate is a hammer, where SQL cancel is more like a tap on the shoulder. Prior to this improvement, the SQL cancel support was only available to ODBC, JDBC and SQL CLI applications. The CANCEL_SQL() procedure extends the SQL cancel support to all application and interactive SQL environments.

When an SQL cancel is requested, an asynchronous request is sent to the target job. If the job is processing an interruptible, long-running machine operation, analysis is done within the job to determine whether it’s safe to cancel the statement. When it’s determined to be safe to cancel the statement, an SQL0952 escape message is sent, causing the statement to terminate.

If it isn't safe to end the SQL statement, or if there’s no active SQL statement, the request to cancel is ignored. The caller of the cancel procedure will observe a successful return code which only indicates that the caller had the necessary authority to request a cancel and that the target job exists. The caller of the CANCEL_SQL() procedure has no programmatic means of determining that the cancel request resulted in a cancelled SQL statement. 

 

Procedure Definition

CREATE PROCEDURE QSYS2.CANCEL_SQL (

IN VARCHAR(28) )

LANGUAGE PLI

SPECIFIC QSYS2.CANCEL_SQL

NOT DETERMINISTIC

MODIFIES SQL DATA

CALLED ON NULL INPUT

EXTERNAL NAME 'QSYS/QSQSSUDF(CANCEL_SQL)'

PARAMETER STYLE SQL ;

 

Example

CALL QSYS2.CANCEL_SQL('483456/QUSER/QZDASOINIT');

 

Authorization

The QSYS2/CANCEL_SQL procedure requires that the authorization ID associated with the statement has *JOBCTL special authority.

 

Description

The procedure has a single input parameter, the qualified job name of the job that should be cancelled. The job name must be uppercased. If that job is executing an interruptible SQL statement or query, the statement will be cancelled. The application will most likely receive an SQLCODE = SQL0952 (-952). In some cases, the failure returned could be SQL0901 or the SQL0952 could contain an incorrect reason code.

This procedure takes advantage of the same cancel technology used by the other SQL cancel interfaces:

• System i Navigator's Run SQL Scripts - Cancel Request button
• SQL Call Level Interface (CLI) - SQLCancel() API
• JDBC method - native Statement.cancel() and toolbox com.ibm.as400.access.AS400JDBCStatement.cancel()
• Extended Dynamic Remote SQL (EDRS) - Cancel EDRS Request (QxdaCancelEDRS) API
• QSYS2/CANCEL_SQL() procedure

If the cancel request occurs during the act of committing or rolling back a commitment-control transaction, the request is ignored.

 

Failures

The procedure will fail with a descriptive SQL0443 failure if the target job isn’t found.

The procedure will fail with SQL0443 and SQL0552 if the caller doesn't have *JOBCTL user special authority.

 

Commitment Control

When the target application is running without commitment control (i.e. COMMIT = *NONE or *NC), the cancelled SQL statement will terminate without rolling back the partial results of the statement. If the cancelled statement is a query, the query merely ends. However, if the cancelled statement was a long-running INSERT, UPDATE or DELETE SQL statement, the changes made prior to cancellation remain intact.

If the target application is using transaction management, the SQL statement will be running under the umbrella of a transaction savepoint level. When those same long running INSERT, UPDATE or DELETE SQL statements are cancelled, the changes made prior to cancellation are rolled back. 

In both cases, the application receives control back with an indication that the SQL statement failed. It’s up to the application to determine the next action.

 

Useful Tool

The QSYS2.CANCEL_SQL() provides a useful tool to database administrators for IBM i machines. Once you have the latest DB Group PTF installed (or the individual PTFs listed below) applied, you can start calling this procedure to stop long-running or expensive SQL statements. Leave the hammer in the toolbox and try calling CANCEL_SQL() instead.

• LICPGM 5761SS1 PTF  SI36317
• LICPGM 5761SS1 PTF  SI36318
• LICPGM 5761SS1 PTF  SI36319

I'd like to thank Scott Forstie for writing this blog post. Scott is a senior software engineer at IBM, and he's the SQL development leader for DB2 for IBM i in Rochester, Minn. Before working on DB2, he worked on UNIX enablement for the AS/400 and S/390 systems. Thanks, Scott!

i Can ... Use Client Special Registers

This week's blog post on client special registers was written by Scott Forstie. Scott is a senior software engineer at IBM, and he's the SQL development leader for DB2 for IBM i in Rochester, Minn. Before working on DB2, he worked on UNIX enablement for the AS/400 and S/390 systems. Thanks, Scott!

Client special registers are a database-specific resource that can be optionally used by application providers to imprint identity or any other useful character-based detail for an application environment. Using client special registers doesn't affect or impact the execution of SQL statements. DB2 for IBM i users can see the client special register values when using DB2 for i tools. When an application or client places meaningful values in the registers, database analysts and database administrators will have a better awareness of the application context.

IBM i 6.1 includes DB2 for i support of client special registers. There are five registers in all and they can be used to brand identity to SQL applications and connections. The register values surface to the user in many places. Through the instrumentation of meaningful, descriptive register values, an application provider will observe improved serviceability and accountability.

The register values are changed using any of the following interfaces. In general, its best to assign the register values during start-up application processing. The assigned values will be inherited across SQL Server mode jobs and across DRDA connections. The registers are different from other SQL special registers in that you aren't allowed to use the SQL SET statement to change the register values. Instead, you need to use one of the following interfaces:

1. Set Client Information (SQLESETI) API
2. SQL Call Level Interface (SQL CLI) - SQLSetConnectAttr()
   (SQL_ATTR_INFO_ACCTSTR, SQL_ATTR_INFO_APPLNAME, SQL_ATTR_INFO_PROGRAMID, SQL_ATTR_INFO_USERID, SQL_ATTR_INFO_WRKSTNNAME connection attributes)
3. ODBC - SQLSetConnectAttr() 
   (ODBC_ATTR_INFO_ACCTSTR, ODBC_ATTR_INFO_APPLNAME, ODBC_ATTR_INFO_PROGRAMID, ODBC_ATTR_INFO_USERID, ODBC_ATTR_INFO_WRKSTNNAME connection attributes)
4. JDBC - setClientInfo() connection method 
   (ClientAccounting, ApplicationName, ClientProgramID, ClientUser, ClientHostName JDBC 4.0 connection properties)
5. SYSPROC.WLM_SET_CLIENT_INFO procedure

Note: The setClientInfo() method is part of the JDBC 4.0 standard. If the application code executes on many databases, beginning to use this method for added value on IBM i won't cause problems for the application when running outside of DB2 for i, as long as those databases support JDBC 4.0.

Similar to other special registers, the client special register values can be queried using SQL. The register values also appear in Database Monitor records and within Database Server joblogs.

Register Name	SQL Special Register Name	Datatype	Database Monitor Column (where QQRID=1000)
Client Application Name	CURRENT CLIENT_APPLNAME	VARCHAR(255)	QVC3001
Client Accounting	CURRENT CLIENT_ACCTNG	VARCHAR(255)	QVC3005
Client Program ID	CURRENT CLIENT_PROGRAMID	VARCHAR(255)	QVC3006
Client User ID	CURRENT CLIENT_USERID	VARCHAR(255)	QVC3002
Client Workstation	CURRENT CLIENT_WRKSTNNAME	VARCHAR(255)	QVC3003

 

System i Navigator 6.1 users will observe client special values woven through the many database features. SQL Details for Jobs, Show Plan Cache Statements, Analyze SQL Performance Monitors and Database Health Center display the register values. IBM clients utilize default values for the client special registers. For example, Run SQL Scripts usage has Client Application Name set to 'System i Navigator - Run SQL Scripts'. Since these values are defaults, any user specified values will take precedence.

Practical Application

The Start Database Monitor (STRDBMON) command includes the capability to pre-filter database monitor records based upon a client special register value. The Interactive SQL (STRSQL) command uses a default value of 'STRSQL' for the Client Program ID. The following command could be used to collect a single database monitor output file of all the SQL executed from within STRSQL. For installations that "lock down" the use of STRSQL, they could use client special register support to review and archive all activity in a seldom-used interface.

 

STRDBMON OUTFILE(QGPL/STRSQLMON1) OUTMBR(*FIRST *REPLACE)

JOB(*ALL/*ALL/*ALL) TYPE(*DETAIL)

COMMENT('FTRCLTPGM(STRSQL)')

< ... later ... >

ENDDBMON JOB(*ALL/*ALL/*ALL)

 

Best Practice

When upgrading to IBM i 6.1, be sure to upgrade your System i Navigator clients to 6.1, to be positioned to extract the full value from the Client Special Register support.

Related Articles and Resources

SOX Auditing of STRSQL and RUNSQLSTM Commands 

IBM DB2 for i 6.1 -- Sophistication simplified 

DB2 for i SQL Reference (Version 6 Release 1) 

Set Client Information (sqleseti() API) 

Viewlet Tour

The following executables provide a visual tour of where to find the client special registers.

Linux (you many need to add execute authority to the downloaded file to run it) 

MacOS 

Windows