Appropriate Content: Informix Documentation Team
IngeHalilovic 060000MPB8 Tags:  onmode dynamic informix configuration onconfic database 3,104 Views
For IBM® Informix® 11.70.xC4, you can update the following additional configuration parameters while the server is running by using the onmode -wf or onmode -wm commands: ALARMPROGRAM, AUTO_REPREPARE, BLOCKTIMEOUT, CKPTINTVL, DBSPACETEMP, DEADLOCK_TIMEOUT, DEF_TABLE_LOCKMODE, DIRECTIVES, DRINTERVAL, DRTIMEOUT, FILLFACTOR, LOGSIZE, LTAPEBLK, LTAPEDEV, LTAPESIZE, MSGPATH, ONDBSPACEDOWN, OPTCOMPIND, RA_PAGES, SHMADD, STACKSIZE, SYSALARMPROGRAM, TAPEBLK, TAPEDEV, TAPESIZE, TBLTBLFIRST, TBLTBLNEXT, TXTIMEOUT, and WSTATS.
See the full list of configuration parameters that you can update dynamically with the onmode -wf or -wm command for Informix 11.70.xC4.
This entry is provided by Tom Houston, our esteemed SQL writer. He will be contributing the occasional entry about how to interpret syntax diagrams as they are used in IBM® Informix® documentation.
The Informix documentation uses syntax diagrams to summarize in a graphic format the syntax tokens -- the keywords, input values, delimiters, and other symbols -- and the order in which they can appear in a valid statement or command. Syntax diagrams that Informix user documents like Administrator's Reference and Guide to SQL:Syntax use a standard graphical notation that many IBM products currently use. Syntax diagrams are described in the Introduction chapter of PDF manuals and in the information centers.
The term syntax note refers to
Several instances of the same parenthetical digit (or digits) within the diagram can reference the same note below the diagram.
This blog entry addresses two potential ways to interpret a syntax note:
This diagram of the CONNECT statement of SQL (for the Informix 11.70 database server) includes two restrictive syntax notes and two cross-reference syntax notes:
1. See Database Environment
2. ESQL/C and DB-Access only
3. See USER Validation Clause
4. ESQL/C only
Here the cross-referencing note (1) that follows the Database Environment syntax segment matches the digit that precedes the
text of the "See Database Environment" note below the diagram, which is a link to the topic that shows the syntax of the Database
Environment syntax segment. Similarly, the cross-referencing note (3) that follows the USER Authentication Clause
syntax segment matches the digit that precedes the text of the "See USER Validation Clause " note below the diagram, which is a link to
the topic that shows the syntax of the USER Authentication Clause syntax segment.
The digit 2 delimited by parentheses (2) preceding the optional USER Authentication Clause syntax segment matches the digit that precedes the "ESQL/C and DB-Access only" syntax note text, restricting that clause to IBM Informix ESQL/C routines and to scripts of the DB-Access utility. This note implies, for example, that the database server will throw an exception if you attempt to run an SPL routine that attempts to connect to a database by using a CONNECT statement that includes this clause.
Similarly, the digit 4 delimited by parentheses (4) preceding the optional WITH CONCURRENT TRANSACTION keywords matches the digit that precedes the "ESQL/C only" syntax note text, restricting that clause to IBM Informix ESQL/C routine. In this diagram, the optional TRUSTED keyword is outside the scope.of syntax note (4), because a CONNECT statement that omits the WITH CONCURRENT TRANSACTION keywords can reach the TRUSTED keyword without following a path that syntax note (4) restricts.
In both instances, the restrictive syntax notes appear immediately above the path of the syntax tokens that the syntax note references, and to the left of those tokens. Syntax notes do not refer to the path above the digit that identifies the note. To apply the same syntax note to several independent paths in a diagram, the parenthetical digit that identifies the syntax note must be repeated for each path.
In an SPL routine, the following SQL statement fails with an error:
CONNECT TO DEFAULT WITH CONCURRENT TRANSACTION TRUSTED;
Even if a default database environment is defined for the current session, this statement fails because CONNECT statements that include the WITH CONCURRENT TRANSACTION keywords are not supported in SPL applications (unless the SPL routine invokes an ESQL/C routine to issue the CONNECT statement).
In summary, a cross-reference syntax note
A restrictive syntax note
Before version 11.70, database operations treated strings stored in all Informix databases as case-sensitive. For example, a query for the string "Central" returns "Central" but not "CENTRAL" or "central". Operations designed to disregard the case of text strings required a bts index or a functional index for each query.
In version 11.70, a database is still created as case-sensitive by default. However, you can use the NLSCASE INSENSITIVE option with the CREATE DATABASE statement to create a database that ignores the case of text strings. For example, querying "Central" returns "Central", "CENTRAL", and "central".
A case-insensitive database ignores letter case only on NCHAR and NVARCHAR data types, but it treats the other built-in character data types (CHAR, LVARCHAR, and VARCHAR) as case-sensitive. You cannot include both case-sensitive and case-insensitive databases in a distributed query.
For more information, see Specifying NLSCASE case sensitivity.
IngeHalilovic 060000MPB8 Tags:  assertion_failure database error server informix corruption 3,091 Views
Did you know that assertion failures are the number one reason for calls to Informix® support? When we in Informix documentation found that out, we decided to shed some light on assertion failures.
What is an assertion failure?
An assertion failure occurs when the database server cannot continue normal processing and must shut down.
What do I do when I get an assertion failure?
If your production server is currently down, contact IBM® Software Support immediately.
To correct an assertion failure:
Tune in next week for part 2 -- how do you prevent assertion failures from happening.
Basic text searching in Informix v11.50 became more versatile, configurable, and faster. See the What's New in Database Extensions for more information.
Process Multiple Basic Test Search Queries Simultaneously
If basic text search queries are slow because multiple users are running queries at the same time, you can add more BTS virtual processors so that queries run simultaneously, each in their own virtual processor. Previously, you could only create one BTS virtual processor and queries ran serially.
Control the Results of a Fuzzy Search with the Basic Text Search DataBlade® module
You can now specify the degree of similarity of search results in fuzzy searches when using the Basic Text Search DataBlade module. Specify a number between 0 and 1, where a higher value results in a higher degree of similarity. To limit results, specify a higher number. To maximize results, specify a lower number. The default degree of similarity is 0.5.
Map Characters for Indexing with the Basic Text Search DataBlade module
You can now map characters in your data to other characters during indexing with the Basic Text Search DataBlade module. For example, you can specify that letters with diacritical marks are indexed as the same letters without marks. You can also standardize inconsistent prefixes or delete character strings from indexed text. To use character maps, include the canonical_maps parameter when you create your bts index.
Default Boolean Operator in Basic Text Search Queries
You can now change the default Boolean
operator between search terms in Basic Text Search queries from OR to AND by
using the query_default_operator parameter when you create a bts index. The default
operator is represented by a blank space between terms. Many popular end-user
search engines use AND as the default operator between search terms, where
end-users expect the search results to contain all their search terms.
Storage for Temporary Basic Text Search Files
You can now specify that temporary files used by the Basic Text Search DataBlade module are stored in a separate sbspace from the one used to store the bts index. Separating temporary files from the bts index might improve query performance.
Track Basic Text Search Query Trends
You can now track what queries are run against your bts index by including the query_log parameter when you create a bts index. You can use query trends information to provide hints to end-users on popular queries or work on optimizing the most popular queries.
Fragment bts Indexes by Expressions
You can now fragment bts indexes by expressions into multiple sbspaces instead of a single sbspace.
Basic Text Search DataBlade module Supports High-Availability Clusters
You can now use the Basic Text Search DataBlade module to perform searches on high-availability cluster servers by creating indexes in sbspaces. Previously, the Basic Text Search DataBlade module only supported the creation of indexes in extspaces, and thus could not participate in any queries on high-availability secondary servers and in backup and restore operations.
Querying XML Attributes with the Basic Text DataBlade module
The Basic Text Search DataBlade module now supports searches on XML attributes in a document repository. The new all_xmlattrs parameter enables searches on all attributes that are contained in the XML tags or paths in a column that contains an XML document.
Support added for a user-defined stopword list
You can create a customized stopword list for frequently occurring words in your data or you can use the default stopword list.
Support added for XML-structured documents
You can use Basic Text Search XML index parameters to manipulate searches of XML data in different ways.
Here are some tips for using the IDS info centers.
To search a particular topic or a set of topics in the 11.50 info center:
1. Select the topic in the left-hand side navigation tree.
2. Click Search Topics on the toolbar above the navigation pane.
3. Choose Search this topic or Search this topic and all subtopics. A search dialog box appears.
4. Enter your query in the Search field, and click OK.
Note that the procedure for doing this in the v10.0 or v11.10 info center is a bit different; you select the topic and then click the arrow next to the topic title.
You might have noticed that no matter where you go in the info center, the URL stays the same. Each topic, however, does have a unique URL. To copy the URL of a particular topic, select the topic title in the navigation tree, right-click it, and choose Copy Shortcut or Copy Link Location. Topics that come from books also have the URL at the very bottom of the topic.
Subscribing to information center updates
You can subscribe to the IDS information center RSS feed to stay up-to-date with the latest updates in the information center. You can add this feed to your preferred RSS reader. When the information center is updated, you will receive a notification in your selected RSS reader with a link to the updated content. Looks for the RSS link on the information center home page.
Improving your search results
Use the following techniques to improve the search results that are returned:
· Search for exact words or phrases by using double quotation marks. For example, if you enter "log file" in the Search field, you search for all occurrences of the string log file. Without the double quotation marks, the search returns topics that contain instances of both the word log and the word file.
· Use wild card characters, such as the asterisk (*) and question mark (?):
o Use an asterisk (*) for multiple unknown or variable characters in the search query. For example, if you enter par*, the search returns topics that contain partly, participate, partial, and other words that begin with par.
o Use a question mark for a single character in the search query. For example, if you enter par?, the search returns topics that contain part, but not partial or partly.
· Use Boolean operators AND, OR, and NOT to make your search more specific:
database AND "log file"
Narrows the search to return any topics that contain both the term database and the phrase log file.
database OR "log file"
Widens the search to return topics that contain either the term database or the phrase log file.
database NOT "log file"
Narrows the search to return any topics that contain database and that do not contain the phrase log file.
database OR "data base" NOT "log file"
Searches for topics that contain either the term database or the phrase data base and that do not contain the phrase log file.
I've been following the Discover Informix events that Louis announces on his blog: http://louistcherian.wordpress.com/. The session yesterday was in Singapore as part of a series of visits to the ASEAN region.
Did you know that, because our product serves a global audience, we offer Informix documentation in various languages? Check out the following links to find documentation in your preferred language and delivery format:
HTML (Information Center): http://ibm.biz/BdxiNW
PDF (books): http://ibm.biz/BdxiN6
IngeHalilovic 060000MPB8 Tags:  informix database blademanager registration timeseries spatial 3,025 Views
In Informix 11.70, the TimeSeries and Spatial DataBlade modules are installed as part of the server instead of being separate products. What does this mean? When you install the database server, by default you'll also install the TimeSeries and Spatial functionality. We refer to DataBlade modules that are included in the server as "extensions". With Informix 11.70, extensions are registered automatically when you first use one of their data types or functions. So you don't need to use BladeManager to register TimeSeries or Spatial (or any of the other built-in extensions). You just install 11.70 and start using TimeSeries or Spatial without any other steps.
Here's the documentation for TimeSeries and Spatial for 11.70:
IngeHalilovic 060000MPB8 Tags:  configuration quick_reference informix onconfig onstat 2,976 Views
The Quick Reference Cards for onconfig.std and onstat have been updated for Informix 11.70. These cards are designed to be printed double-sided on legal-sized paper (8.5" by 14") and folded into a four-sided pamphlet. They look best printed in color, especially the onconfig.std card, which shows version numbers in red.
IngeHalilovic 060000MPB8 Tags:  informix time_series data_loading time-based database timeseries smart_meters 2,973 Views
One of the main focus areas for the IBM® Informix® 11.70.xC4 release is the Informix TimeSeries solution. You can read about all the new features for the Informix TimeSeries solution here, but I want to expand a bit on the new way to load time series data. In previous releases, you could load time series data in a number of different ways, such as by using virtual tables, by creating a load file with a very specific format, or by using a TimeSeries function. In 11.70.xC4, you can use the IBM Informix TimeSeries plug-in for Data Studio to load data from a standard, delimited input file or an LSE file.
After you create your Informix TimeSeries table, all you have to do to load data into it with the TimeSeries plug-in is to:
This example leads you through implementing the Informix TimeSeries solution with the data in the stores_demo database.
Want to hear about news, events, and other fun stuff about Informix? Like IBM Informix Dynamic Server (IDS) on facebook.
96QY_Pat_Smith 27000196QY 2,967 Views
If you install version 11.50xC6 or a later version of Informix and then upgrade to another new Informix version or fix pack, you can enable the server to use the onrestorept utility to restore the server to a consistent, pre-upgrade state if the upgrade fails. If you enable the server to use this utility, you can undo changes made during the upgrade in minutes (and in some cases, in seconds). Previously, if a fix pack upgrade failed, you had to restore the database by using a level-0 archive.
You use the new CONVERSION_GUARD and RESTORE_POINT_DIR configuration parameters, which are documented in the IBM Informix Administrator's Reference, to specify information that the onrestorept utility can use if an upgrade fails.
For information on the onrestorept utility and setting up the server for it, see the following topics in the IBM Informix Migration Guide:
IngeHalilovic 060000MPB8 Tags:  administration memory dba database locks informix sessions onstat logspace tempspace transactions 2,951 Views
With IBM® Informix® 12.10.xC5, you have a lot more control over session resources. Besides setting session environment variables to control the current session, you can set configuration parameters to limit all sessions and prevent blocked transactions.
You can specify to end sessions that exceed a specified amount of shared memory or temporary storage space:
You can specify to roll back transactions that are too large or take too long:
Session limits do not apply to a user who holds administrative privileges, such as user informix or a DBSA user.
Transactions and sessions that exceed a set limit are terminated by the session_mgr thread. The session_mgr thread starts when the database server starts, and remains inactive until a session limit is exceeded.
Monitor sessions with the onstat -g ses command.
IngeHalilovic 060000MPB8 Tags:  performance warehouse search database oat install informix 2,919 Views
The Informix® 11.70 information center has been updated with 11.70.xC2 information!
The new features in IBM® Informix 11.70.xC2 are:
Read the What's new in Informix topic in the information center for a short description of each new feature and links to where they are documented in detail.
IBM® Informix® shard cluster architecture is very flexible:
As of Informix 12.10.xC6, you can now run SELECT statements in sharded queries in parallel instead of serially on each shard. Parallel sharded queries return results faster, but also have the following benefits:
To enable parallel sharded queries, set the new SHARD_ID configuration parameter in the onconfig file to a unique value on each shard server in the shard cluster. Also set the new sharding.parallel.query.enable=true and sharding.enable=true parameters in the wire listener configuration file for each shard server. You can customize how shared memory is allocated for parallel sharded queries on each shard server by setting the new SHARD_MEM configuration parameter. You can reduce latency between shard servers by increasing the number of pipes for SMX connections with the new SMX_NUMPIPES configuration parameter.
Also see this cool demo of parallel sharding: https://ibm.biz/BdHaru