LinkedIn

LOAD and UNLOAD functions

Abstract

The Informix statements LOAD and UNLOAD allow data to be transferred between a flat file and a database, but are in fact only implemented in certain clients such as DB-Access, 4GL variants, and AGS Server Studio. You may therefore need functions in Informix Dynamic Server (IDS) that do this, such as when coding in Java with JDBC. Furthermore, external tables are much lighter and quicker for this purpose, so you might in any case prefer the functions described in this article, which use them in Informix Stored Procedure Language (SPL).

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailEasy IWA: Part 2 Creating an IBM Informix Warehouse Accelerator under 12.10.FC4 Default ThumbnailInformix Table Restore
LOAD and UNLOAD functions2023-05-16T15:35:33+01:00

View dependencies

Abstract

Informix supports views like other database products, which behave much like tables but are in fact SELECT statements stored for convenience and reuse. If a table or view is dropped and recreated during a schema change, any views in the same database that depended on it will have been lost. This article covers this problem, and includes SQL to list dependencies and definitions of views.

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailInformix Table Restore Default ThumbnailInformix “cdr migrate server”
View dependencies2023-01-25T09:52:30+00:00

Enhancing DB-Access

Abstract

Informix database server products, including both Standard Engine and Dynamic Server, have for decades been delivered with a character interface tool for database exploration and SQL execution. This is referred to as “DB-Access” in the documentation, and is invoked with “dbaccess” from the operating system command line or within scripts, as described in the latest DB-Access User’s Guide. This article covers some lesser known and very useful environment variables that improve its usability, and a way to enable command history and editing when used directly as an SQL CLI.

[Read More…]

Related Technical Articles:

Default ThumbnailMaking Microsoft SQL Linked Server and DB2 work Default ThumbnailEasy IWA: Part 3 Creating and Loading a Data Mart under IBM Informix 12.10.FC4 and IBM Informix Warehouse Accelerator Default ThumbnailInformix “cdr migrate server”
Enhancing DB-Access2022-11-23T09:11:53+00:00

Listing and limiting idle time of Informix sessions

Abstract

Some systems need user sessions terminated regularly – whether human or automated – to prevent eventual excessive memory consumption. This article provides an Informix Dynamic Server “sysmaster” query and script to identify those that are idle, and which therefore are most likely no longer needed and can be terminated.

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailGenero 3.20 Remote Development Default ThumbnailMaking Microsoft SQL Linked Server and Informix Standard engine work
Listing and limiting idle time of Informix sessions2022-06-28T14:29:02+01:00

Dropping an Informix database in use

Abstract

Scripts are often created to refresh test databases – either scheduled overnight or on demand – so that QA or development teams can validate or debug schema and application changes against the latest realistic data. An easy solution is to run a full instance restore using ontape or onbar from the last production archive, but sometimes many copies of the database are required for different projects or testing stages running in parallel, and avoiding the overhead of a separate instance each can be desirable. This means that, before any database can be restored using dbimport or similar, it first needs to be dropped, which is impossible when in use, as an exclusive database lock must be applied. This article provides a script to achieve that, even if there are continual concurrent connection attempts by applications.

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailEasy IWA: Part 1 – The TPCH database, data generation and IBM Informix 12.10.FC4 Default ThumbnailInformix “cdr migrate server”
Dropping an Informix database in use2021-12-21T12:22:25+00:00

How to list temp dbspace contents

Updated November 2021

Abstract

If an Informix instance warns that one of your temp dbspaces is full, it is useful to have SQL quickly to list what it contains, or to be run automatically if dbspace usage goes over a maximum acceptable percentage. Such a “sysmaster” query is provided in this article.

[Read More…]

Related Technical Articles:

Default ThumbnailIBM Guardium v9.5 VM Installation Default ThumbnailInformix SQL Capture Techniques Default ThumbnailEasy IWA: Part 3 Creating and Loading a Data Mart under IBM Informix 12.10.FC4 and IBM Informix Warehouse Accelerator
How to list temp dbspace contents2021-11-26T14:22:48+00:00

Informix Table Restore

Abstract

However you back up your Informix Dynamic Server (IDS) instance, a long-standing method exists to restore a single table as at a given point in time. This is described elsewhere, but some awkward coding is required. If someone has accidentally emptied a production table, you would want a very fast way to reinstate it with minimal manual steps. This article provides a script to help achieve that, together with setup and example usage.

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailInformix “cdr migrate server” Default ThumbnailMaking Microsoft SQL Linked Server and Informix Standard engine work
Informix Table Restore2021-11-19T15:09:26+00:00

Informix assert file lock analysis

Abstract

This article provides a script to analyse what database objects were locked when IBM Informix Dynamic Server (IDS) “asserts” (saves an evidence file) including what sessions were waiting on another, which is particularly useful to diagnose deadlocks.

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailListing Informix Locks Default ThumbnailInformix Stored Procedure for Mass Delete
Informix assert file lock analysis2021-11-23T14:02:16+00:00

InformixHQ Startup Sensor

Abstract

This article provides a stored procedure to run InformixHQ (HQ) Java processes automatically whenever IBM Informix Dynamic Server (IDS) has been restarted, as originally posted on the community forum here. It follows on from the previous article InformixHQ Setup and uses the shell script “informix-control.sh” it contains which starts or stops HQ.

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries InformixHQ Setup InformixHQ Repository
InformixHQ Startup Sensor2021-11-23T14:01:42+00:00

Revalidating Informix foreign key data

Abstract

When testing a database migration to a new platform that is not compatible, for example with a different OS or locale, you cannot restore from a level 0 archive on the new server. Data must be copied either via flat files or directly using SQL tools. If this is from a live database that is being updated and cannot be exclusively locked (which rules out dbexport), you cannot guarantee consistency of the data being reloaded, so NOVALIDATE must be set to ensure foreign keys can be reapplied. This article provides stored procedure language (SPL) code to identify and delete any resulting invalid data.

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailInformix Stored Procedure for Mass Delete Default ThumbnailSub-queries: a faster alternative to NOT EXISTS
Revalidating Informix foreign key data2021-11-23T14:01:23+00:00
Terms & Conditions | Privacy Policy | Cookie Policy | Anti-Spam Policy | Sitemap
Copyright © Oninit Consulting Limited | Website design by Tweak Marketing
LinkedIn
Go to Top