Informix International User Group - IDS Forum

Subscribe to Informix International User Group - IDS Forum feed Informix International User Group - IDS Forum
International Informix Users Group Forum: IDS Forum
Updated: 30 min 16 sec ago

Connection Manager using SSL

Fri, 18/10/2019 - 16:47
I am trying to connect connection manager (conman1) to the IDS (mambaps) using
SSL. I have configured the IDS correctly and tested through dbaccess from the
connection manager server and from a windows machine with ODBC and it connects
with no problems. My issue is when I try to start the connection manager I
receive a -28015 error (An error occurred while reading the Secure Sockets
Layer configuration file (conssl.cfg)). I have the conssl.cfg file in the
$INFORMIXDIR/etc folder and the permissions are good. Any help would be
appreciated.

*contents of conssl.cfg file:
SSL_KEYSTORE_FILE /opt/IBM/informix/ssl/conman1.kdb
SSL_KEYSTORE_STH /opt/IBM/informix/ssl/conman1.sth

*contest of the /opt/IBM/informix/ssl folder:
conman1.crl conman1.kdb conman1.rdb conman1.sth

*gsk8capicmd_64 -cert -list -db conman1.kdb -stashed output:
Certificates found
* default, - personal, ! trusted, # secret key
! mambaps_ssl
- rmca_1_ssl
*- conman1_ssl




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41834]

*******************************************************************************
Categories: Informix

TOP 50 SQL Queries by CPU/Memory/IO

Thu, 17/10/2019 - 23:33
Informix Version : 12XC12
Operating System : Linux

Hi all

I am looking for tools or script that can give the the top X queries that
utilize the most
1) CPU
2) Memory
3) I/O

on daily basis.

Talking to a support engineer for advise, the only recommended tools is
InformixHQ which only available in V14.00 or V12.XC14. I know I can download
V14 and just extract few jar files but it was't recommended by the engineer
that I working with since we are at V12.XC12 (upgrade is not an option now).

InformixHQ only consist of some feature that we wanted and further
customization is needed i.e. SQL script and etc.

Questions
1) Is there a tool out there that can give me this quick win ? How about
sentinel ?

2) Anyone have a customized script that can give us what we wanted?

Any help is much appreciated.

Thanks




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41833]

*******************************************************************************
Categories: Informix

ADO - activex data object

Thu, 17/10/2019 - 15:01
What do I require, to connect "ado activex data object" to Informix ?

AIX 7.2

IBM Informix Dynamic Server Version 12.10.FC9W1

Regards
Dirk




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41832]

*******************************************************************************
Categories: Informix

Connection Manager Redundancy with DRDA Connection

Wed, 16/10/2019 - 15:42
I have two Connection managers configured with two SLA entries using REDIRECT,
one for the native SQLI connection (onsoctcp) and one for DRDA connection
(drsoctcp) connection to just on IDS for testing. Everything looks good
between the connection managers and the IDS. When I test my .net apps using
the SQLI connection fail over to the 2nd connection manager works fine, but
when I test with the DRDA connection it seems to just hang when opening the
connection using the fail over connection manager. When looking at the log for
connection manager I see the connection getting redirected. I do not see any
errors either in the connection manager log or the IDS log. After about 5 mins
or so the receive this error in my app:
"ERROR [08001] [IBM] SQL30081N A communication error has been detected.
Communication protocol being used: "TCP/IP". Communication API being used:
"SOCKETS". Location where the error was detected: "<server ip>". Communication
function detecting the error: "connect". Protocol specific error code(s):
"10061", "*", "*". SQLSTATE=08001"




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41831]

*******************************************************************************
Categories: Informix

Get largest table in database

Tue, 15/10/2019 - 20:22
Hi all,

I need to get the largest tables in terms of size/ rows from informix
database. Do we have any query or any specific procedure that we can get the
tables list?

Thanks in advance




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41830]

*******************************************************************************
Categories: Informix

Re: PDQ mixed with OLTP

Thu, 03/10/2019 - 10:01
You need to check the explain plan to see whether you would benefit from
parallel processing. I believe that if you set PDQ you can still benefit from
from access to the memory grant manager even without a parallel query. You can
check this with "onstat -g mgm" while your query is running.

2 MB for DS_TOTAL_MEMORY is really not very much. DS_TOTAL_MEMORY is part of
SHMVIRTSIZE but I think it is a limit rather than a pool. SHMTOTAL is a limit
imposed on the size of SHMVIRTSIZE. Do you monitor use of SHMVIRTSIZE on your
system? The most basic way is using "onstat -g seg".

IBM doesn't document how the memory allocations work very well, presumably
part of the policy of not documenting some internal workings which could be
subject to change. What I can say is that I have DS_NONPDQ_QUERY_MEM set to 16
MB on one system and there are many sessions using much less than 16 MB of
memory. I believe this would only be allocated to do a sort.

Happy for someone with a better knowledge of the server workings to chip in!

Btw this forum is moving to:

https://community.ibm.com/community/user/hybriddatamanagement/communities/community-home?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365

Ben.




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41829]

*******************************************************************************
Categories: Informix

Re: PDQ mixed with OLTP

Wed, 02/10/2019 - 02:06
Thanks for the reply BENJAMIN THOMPSON sadly as per checking we have 1
fragmented table but it is not included in the batch process =( does this mean
PDQ would probably not applicable? DS_NONPDQ_QUERY_MEM is the counterpart for
non-PDQ queries correct? is it part of the SHMTOTAL and different from the
BUFFER space allocation? as I've checked value for DS_NONPDQ_QUERY_MEM is 512
KB, given below memory allocation how much can I adjust for the NONPDQ query?
How does informix allocate memory from DS_NONPDQ_QUERY_MEM to each query? also
as for the tempdb query will have to schedule to check if it is utilized upon
batch processing.

IBM Informix Dynamic Server Version 12.10.FC8
total memory is 32 GB
16 GB allocated for Buffer
4 GB in SHMVIRTSIZE
2MB for DS_TOTAL_MEMORY
512 KB for DS_NONPDQ_QUERY_MEM




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41828]

*******************************************************************************
Categories: Informix

Re: PDQ mixed with OLTP

Tue, 01/10/2019 - 10:19
Hi Lester,

Yes you can use PDQ in the latest versions, not sure what you might have read.

The first step would be to determine whether your batch process will benefit
from parallel processing by examining the query plans. A "set explain on" or
"set explain on avoid_execute" would determine this and you would be looking
for the keyword "parallel" in the query plan. If you haven't got partitioned
(fragmented) tables this is not very likely.

Your post concentrates on the memory management aspect. Typically large sorts
(order by, group by, distinct etc.) benefit from more memory. Are you seeing
use of temp dbspaces while the query runs?

If you have 12.10.xC8+ you can monitor use of temp space in real time with:

SELECT
i.sid,
hex(i.flags) flags,
hex(i.partnum) partition,
trim(n.dbsname) || ":" || trim(n.owner) || ":" || trim(n.tabname) table,
i.nptotal allocated_pages
FROM
sysmaster:systabnames n,
sysmaster:sysptnhdr i
WHERE
(sysmaster:bitval(i.flags, "0x0020") = 1) AND
i.partnum = n.partnum;

If you're not seeing temp space usage it's unlikely any memory adjustments
will help. It might just be a slow query.

If you won't get any benefit from parallel processing, have you considered
adjusting DS_NONPDQ_QUERY_MEM as an alternative to using PDQ? You would also
have to increase DS_TOTAL_MEMORY. Both of these parameters are dynamically
configurable.

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.adref.doc/ids_adr_0064.htm

Ben.




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41827]

*******************************************************************************
Categories: Informix

PDQ mixed with OLTP

Tue, 01/10/2019 - 03:04
Hi,

First of all good morning to all of the members just want to ask if anyone had
done mixing PDQ with OLTP on their database? Our database is mostly OLTP with
a batching process running every 12 MN of weekdays up until 3AM, I am
wondering if I can use PDQ for the batching process then the rest of the
memory will be allocated to the buffer cache for oltp purposes. Will this
work? will the batch process improve? as of now no PDQ is running on the
server, total memory is 32 GB with 16 GB allocated for Buffer and 4 GB in
SHMVIRTSIZE then 2MB for DS_TOTAL_MEMORY. I am planning on increasing
DS_TOTAL_MEMORY to initially 50% of SHMVIRTSIZE on our staging server to test
if the process will improve.

If this is the case how can I instruct the queries to use PDQ? I've red
somewhere that PDQPRIORITY no longer works after 7.1 is this true?




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41826]

*******************************************************************************
Categories: Informix

How to get the hour of a DAY TO MINUTE interval?

Fri, 27/09/2019 - 17:53
Dear colleagues:
I have to get the total hours worked by "N" employees for a year.

I have the entry and exit records of each of them, which I obtain from an
electronic clock. The date format is a DATETIME YEAR TO FRACTION (3).

When I subtract the entry from the exit, I get an INTERVAL of this type: "0
00: 00: 00.000" where the first data is for the day, and the rest for hours,
minutes, seconds and milliseconds.

The problem arises when I try to use the hours to add them and go totaling.

In a normal query, it is easy to get the hours and minutes, for example:

"SELECT TO_CHAR (EXTEND (FechaFichada, HOUR to HOUR), '% H') FROM xxFichadas
WHERE LegLegajo = 97 and IdFichada IN (2502323, 2502876);"

This query returns me the time of dialing, but if I want to use it in a Stored
Procedure, like this:
"LET vHoras = TO_CHAR (EXTEND (vHorasTrabajadas, HOUR TO HOUR), '% H');"

It throws me an error: "SQL Error (-1260): It is not possible to convert
between the specified types."

I clarify that the variable "vHorasTrabajadas" is declared as: "INTERVAL HOUR
TO MINUTE;"

From what I have seen, the EXTEND function does not work for small intervals
(if they are part of a complete DATETIME, yes).

My Informix is ​​an IDS 7.31 TD6, so the CAST function is not
supported.
Do any of you know any way to isolate hours and minutes separately to
accumulate them and then show them?

Grateful




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41825]

*******************************************************************************
Categories: Informix

Re: Problems with LOAD FROM (SOLVED)

Thu, 26/09/2019 - 23:02
Dear Luis:

The problem is already solved.

After reviewing once and a thousand times both the text file and the Informix
table, I detected in the text editor where the file was open, that the
encoding of the file was UTF-16. I proceeded to change it to ANSI, I recorded
it, and I could import it without problems.
Apparently, the exports made by SQL Server are the UTF-16, which my Informix
7.31 TD6 engine (old man) does not like.

Thank you very much for your attention.

A hug!

Gustavo




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41824]

*******************************************************************************
Categories: Informix

Re: Problems with LOAD FROM

Thu, 26/09/2019 - 16:49
If you could post the table definition and the offending line from the flat
file, it would greatly help us to make more informed suggestions. Also the
Informix version and operating system could give us some clues.

Best regards,
Luis Marques.




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41823]

*******************************************************************************
Categories: Informix

Problems with LOAD FROM

Wed, 25/09/2019 - 23:55
Dear colleagues:

I ask for your help with a topic as trivial as it is to import data from a
plain text file, but that is driving me crazy.

I need to import data from a SQL Server 2005 to Informix. Just working with
SQL Server is an odyssey, but I managed to generate a .csv file with the data
I need. They are more than 1.7M of records of five (5) columns, and are all
delimited with "|" pipe

I created a temporary table with these five columns, but when I execute the
LOAD FROM ... INSERT INTO ... I get an error -846 in row one (1) that
indicates that the columns of the flat file do not match those of the table.

I have checked many times and there is no error in the file, as well as in the
table. I have opened the file with Excel, and it does it perfectly, obviously
loading part of the file because of its limitations.

The second column of the file is a DATETIME YEAR TO FRACTION (3), so I thought
it could cause me a problem, but if I treat it as VARCHAR it throws the same
error.

I hope someone can give me a hand because I don't know where to look anymore.

Grateful




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41822]

*******************************************************************************
Categories: Informix

Re: BTS/C-Lucene issues

Fri, 20/09/2019 - 23:54
Thanks Alexandre.
However, with testing, the backslash simply breaks the query into 2 parts, and
then drops the * entirely.

So searching for bts_contains(full,"TERM") and bts_contains(full,"TERM\*")
return the exact same results.

What I expect would be that "TERM\*" returns all TERM, as well as TERMINATION,
TERMINAL, etc. as bts_contains(full,"TERM*") does **when it isn't failing**.

Thanks,
Michael




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41821]

*******************************************************************************
Categories: Informix

Downgrade informix client tools

Thu, 19/09/2019 - 09:21
Hi All,

We are having issues with running sperform tools on informix client. It seems
that these servers have a higher version
of “sperform” than ISQL. We are not able to execute some forms on these
servers.

IBM INFORMIX-SQL Version 7.51.FC2 <-- 7.51.FC2 This higher version on APP
servers and seems to be causing issues
Software Serial Number RDS#N000000

IBM INFORMIX-SQL Version 7.50.FC4 <-- 7.50.FC4
Software Serial Number RDS#N000000

My requirement is downgrade the higher version and install the lower version.

IBM INFORMIX-SQL sperform Version 7.51.FC2 --- need to downgrade.
IBM INFORMIX-SQL sperform Version 7.50.FC4 need to install

Please guide me regarding right approach.

Thanks

Urgent help highly appreciated.




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41820]

*******************************************************************************
Categories: Informix

Re: InformixHQ - Backup sensor

Wed, 18/09/2019 - 20:27
The result is:
InformixHQ Alert on ol_serverprd - Last level-0 backup: Last Level 0 Backup of
8 (1568761201.0) is greater than 24.0

What kind is this metric "156876120"?




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41819]

*******************************************************************************
Categories: Informix

InformixHQ - Backup sensor

Wed, 18/09/2019 - 20:08
Hello.
I have set InformixHQ Backup Sensor to notify me whenever Backup Level 0 is
greater than 24 (hours).
But is always notifying me, does anyone know the exact value for this metrics
in InformixHQ?




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41818]

*******************************************************************************
Categories: Informix

RSS over SSL

Tue, 17/09/2019 - 16:33
Hi,

I am trying to setup one RSS server using SSL, can someone give some help
about this?

Thanks in advance,

SP




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41817]

*******************************************************************************
Categories: Informix

Re: Problem with backup

Tue, 17/09/2019 - 15:56
Original post:

Hello,

every time we release a copy with onbar , the Database server gets blocked one
to three minutes. Is this normal ?

Our version is 12.10.FC3WE and the command is onbar -b -w

Thank

Response:

The answer is possibly. At the start of every backup, the server has to
perform a blocking checkpoint. This is different than what should be most
checkpoints happening on a system. Most checkpoints would be what's called
interval or non-blocking, meaning that the system is still able to perform
write activity while the buffer pool flushing is happening, so there is
normally only a very small block time. Durning a blocking checkpoint, the
server blocks write activity for the duration of the entire checkpoint, which
would include the buffer pool flushing. So if you h ave large buffer pools
with high values for LRU min/max (which control page flushing between
checkpoints) then yes, it may be normal for the server to have to block that
long to complete all the IO required for the checkpoint. You should be able to
look at the output of onstat -g ckp to see if the checkpoint that's happening
at the start of the backups being kicked off is taking a long time because of
"flush" time (that's the name of the column in the -g ckp output, and it is
the time it took the server to flush all the dirty buffers in the buffer
pool/s to disk. Anyway, I'd start looking there to get a better idea if this
is normal or not.

Jacques Renaut
HCL Informix Advanced Support




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41816]

*******************************************************************************
Categories: Informix

Problem with backup

Tue, 17/09/2019 - 10:27
Hello,

every time we release a copy with onbar , the Database server gets blocked one
to three minutes. Is this normal ?

Our version is 12.10.FC3WE and the command is onbar -b -w

Thanks




*******************************************************************************

To post a response via email (IIUG members only):

1. Address it to ids@iiug.org
2. Include the bracketed message number in the subject line: [41815]

*******************************************************************************
Categories: Informix

Pages