Troubleshooting

Problem

Connections to Informix Dynamic Server (IDS) hang or take a long time to connect. Other users who are already connected do not see a performance problem. Once a user is connected there is no performance problem.

Symptom

Connections to Informix Dynamic Server (IDS) hang or take a long time to connect. Other users who are already connected do not see a performance problem. Once a user is connected there is no performance problem. Users connecting by way of shared memory do not experience a connection hang. Only users using network connections see this problem.

Cause

There are not enough files descriptors allocated for IDS to effectively handle the number of network connections. See the Related information section for a link to more information regarding file descriptors.

Note: This is only one possible cause.

Resolving The Problem

1. Increase the number of file descriptors available to a process. Ask your operating system administrator to increase the number of file descriptors .The suggested are values 2048, 4096, or unlimited. You can temporarily increase the number of file descriptors by running the ulimit -n command:

2. Restart IDS after the file descriptor increase is implemented.

Note: If this does not resolve the problem then look for the Collecting Data document that will help you collect the correct troubleshooting data for a hang. After you collect this data contact technical support.

Also look at this document; Slow connect time to Informix when using TCP/IP

https://www.ibm.com/support/pages/connections-ids-hang

Connections to IDS hang

www.ibm.com

'Informix > informix troubleshooting' 카테고리의 다른 글

Slow connect time to Informix when using TCP/IP (0)	2023.10.12
A possible cause for slow connection to the database server (0)	2023.10.12
104 ISAM error: too many files open. (0)	2023.01.13
Where do I start with Diagnosis of Generally Slow Performance on Informix Servers? (0)	2023.01.11
Techniques for troubleshooting problems (0)	2021.06.13

Troubleshooting

Problem

Client applications using the TCP/IP protocol to connect to Informix experience poor performance during connection. Once the connection has been established the performance is improved.

Cause

During connection establishment, the IP address of the client is used by the IDS server to look up its host name. The method used to accomplish this is dependent on the configuration of the operating system. If the Domain Name System (DNS) is used, an operation known as a reverse query is used to map the IP address to host name. If the configuration of the DNS server is incomplete this type of query may take some time.

The command line utility nslookup may be used to perform DNS queries. If a reverse lookup operation takes a long time to complete using nslookup then the IDS server will also take a long time to complete the same operation.

The first example shows a reverse lookup operation for an IP address that is known to the DNS server and completes immediately. The second example uses an IP address that is unknown taking several seconds before returning an error.

C:\>nslookup 1.2.3.4
Server: host1.domain.com
Address: 9.8.7.6

Name: host222.domain.com
Address: 1.2.3.4

C:\>nslookup 9.69.1.126
Server: host1.domain.com
Address: 9.8.7.6

DNS request timed out.
timeout was 2 seconds.
*** Request to host1.domain.com timed-out

The lookup for the service name could be taking a while.

Another cause may by the use of IPv6 addresses with DNS servers that are not configured for IPv6.

Diagnosing The Problem

To isolate the problem to tcp/ip connections try to connect by way of a shared memory connection. If the connection is now completed quickly then the problem outlined above may be the cause of poor connection performance.

Follow this link if you need instructions for setting up shared memory connections:

A shared-memory connection (UNIX)

Resolving The Problem

Refer to your operating system documentation for details of how to configure DNS. If the DNS server runs on the Microsoft Windows® platform then it is possible that the zone used to implement reverse lookup is not implemented. Details regarding DNS on the Windows operating system can be found in the related URLs below. Ensure that the reverse-lookup zone is populated for all clients that connect to the IDS server.
Ensure that local hostname resolution is performed before other methods of hostname resolution (NIS, NIS+, DNS, and so on). Files like these are used on various operating systems to determine lookup order:

/etc/netsrv.conf
/etc/irs.conf
/etc/nsswitch.conf
Reconfigure your operating system to not use DNS, for example by including the host names and IP addresses of all client machines in the local hosts file /etc/hosts or %WINDIR%\system32\drivers\etc\hosts on Windows.

Change the host name in the sqlhosts file to an IP address.
Change the service name in the sqlhosts file to the port number.
Connections to IDS hang

https://www.ibm.com/support/pages/slow-connect-time-informix-when-using-tcpip

Slow connect time to Informix when using TCP/IP

www.ibm.com

'Informix > informix troubleshooting' 카테고리의 다른 글

Connections to IDS hang (0)	2023.10.12
A possible cause for slow connection to the database server (0)	2023.10.12
104 ISAM error: too many files open. (0)	2023.01.13
Where do I start with Diagnosis of Generally Slow Performance on Informix Servers? (0)	2023.01.11
Techniques for troubleshooting problems (0)	2021.06.13

Troubleshooting

Problem

A problem with DNS lookup can cause slow connection to database server. This article explains one scenario and a possible solution for that problem.

Symptom

Connection request to database server from dbaccess is trying to access DNS server for hostname resolution, after it found the same in the local host file.

On certain Operating System (for example: Linux) you may noticed dbaccess (and other client applications) always doing DNS lookup while connecting to a database, even after found out the hostname or IP address in the local host file. This behavior sometimes caused slow connection, if you have problem related to DNS. Following is an excerpt of strace output shows the sequence of file accessed by a dbaccess request:

Cause

Traditionally, hostname and service name resolution were performed by functions such as gethostbyname(), getservbyname() etc. These traditional lookup functions are still available, however those are not forward compatible to IPv6. Instead, the IPv6 socket API provides new lookup functions that consolidate the functionality of several traditional functions. These new lookup functions are also backward compatible with IPv4, so a programmer can use the same translation algorithm in an application for both the IPv4 and Ipv6. The getaddrinfo() is the new primary lookup function and a connection request from the dbaccess ultimately calls this socket API. You can pass several parameters to the getaddrinfo(), one of those parameter is addrinfo structure. By default, dbaccess passes value “AF_INET6” for addrinfo.ai_family. The ai_family field indicates the protocol family associated with the request, and will be PF_INET6 for IPv6 or PF_INET for IPv4.

If the ai_family set to AF_INET6 (IPv6) the getaddrinfo() will search the DNS every time. If the ai_family set to AF_INET, then it don't query the DNS server. You can consult the 'man' page for getaddrinfo() for detailed information.

Beginning with Informix 10.00.xC4 and Client SDK 2.90.xC4, the database server checks, on startup, whether IPv6 is supported in the underlying operating system. If IPv6 is supported it is used. If the underlying operating system does not support IPv6, the IPv4 address is used.

Diagnosing The Problem

You can use the attached 'C' program to reproduce and verify the problem outside Informix.

/*
* gt_adr_info.c - An example of using getaddrinfo() function to validate IPV6.
*                 compile: cc -o gt_adr_info  gt_adr_info.c
*                 usage: gt_adr_info <HostName>
*/

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <netdb.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <arpa/inet.h>

int
lookup_host (const char *host)
{
  struct addrinfo hints, *res;
  int err;
  char addrstr[100];
  void *ptr;
  char *result;

  memset (&hints, 0, sizeof (hints));

  result = getenv("IFX_DISABLE_IPV6");

  if (result != NULL)
      hints.ai_family = AF_INET;
  else
      hints.ai_family = AF_INET6;

  hints.ai_flags = AI_PASSIVE;

  if ((err = getaddrinfo(host, NULL, &hints, &res)) != 0)
    {
      perror ("getaddrinfo");
      return -1;
    }

  printf ("HostName: %s\n", host);
  while (res)
    {
      inet_ntop (res->ai_family, res->ai_addr->sa_data, addrstr, 100);

      switch (res->ai_family)
        {
        case AF_INET:
          ptr = &((struct sockaddr_in *) res->ai_addr)->sin_addr;
          break;
        case AF_INET6:
          ptr = &((struct sockaddr_in6 *) res->ai_addr)->sin6_addr;
          break;
        }
      inet_ntop (res->ai_family, ptr, addrstr, 100);
      printf ("IPv%d address: %s (%s)\n", res->ai_family == PF_INET6 ? 6 : 4,
              addrstr, res->ai_canonname);
      res = res->ai_next;
    }

  return 0;
}

int
main (int argc, char *argv[])
{
  if (argc < 2)
    exit (1);
  return lookup_host (argv[1]);
}

gt_adr_info.c.txt

How to use the attached 'C' program?

1. Save the attached file as 'gt_adr_info.c'.

2, Compile the 'C' program using following command:

3. Run it as 'gt_adr_info <HostName>', where HostName is the machine name that you want to connect.

Resolving The Problem

In case of a problem with DNS lookup and encountering slow connection to database, you may use the environment variable IFX_DISABLE_IPV6 (IFX_DISABLE_IPV6=1) to disable Ipv6 and this will set the ai_family to AF_INET only and will not do subsequent query to the DNS server.

https://www.ibm.com/support/pages/possible-cause-slow-connection-database-server

A possible cause for slow connection to the database server

www.ibm.com

'Informix > informix troubleshooting' 카테고리의 다른 글

Connections to IDS hang (0)	2023.10.12
Slow connect time to Informix when using TCP/IP (0)	2023.10.12
104 ISAM error: too many files open. (0)	2023.01.13
Where do I start with Diagnosis of Generally Slow Performance on Informix Servers? (0)	2023.01.11
Techniques for troubleshooting problems (0)	2021.06.13

Symptom

DML operations failing with ISAM Error -104

Cause

The 32K File Descriptors (open tables) per thread limit has been reached

Diagnosing The Problem

Use onstat -g opn and look at the ucount column

IBM Informix Dynamic Server Version 11.70.FC2 -- On-Line -- Up 14:23:05 -- 2964472 Kbytes
tid rstcb isfd op_mode op_flags partnum ucount ocount lockmode
671522 0x000000009c9e0a20 0 0x00000400 0x00000397 0x004000da 2 2 0
671522 0x000000009c9e0a20 1 0x00000002 0x00000003 0x004000da 2 2 0
671522 0x000000009c9e0a20 2 0x00000400 0x00000397 0x00100003 2 2 0
671522 0x000000009c9e0a20 3 0x00000002 0x00000003 0x00100003 2 2 0
671522 0x000000009c9e0a20 8 0x01000400 0x00000407 0x0090003c 32692 0 0
671522 0x000000009c9e0a20 9 0x01000440 0x00000797 0x0090003d 32692 0 0

(please note there are 2 partitions - 0x0090003c, 0x0090003d - each of which is opened 32692 times)

Resolving The Problem

*Check the application logic.
*Check that UDRs are not compiled with PDQ set

If you have a user defined routine (UDR) which is called as a
part of many update/delete triggers on different tables
and if the application logic in the update/delete triggers
mimics some kind of hierarchy (so a modification in one table
can cause many changes in many other tables), the UDR can get
called multiple (thousands) times in one session.

If the UDR
1) contains a query which needs to materialize some view
2) was created with PDQPRIORITY > 0 (or the last 'update
statistics low [for procedure]' command was run with PDQPRIORITY
> 0)

each of its invocations materializes the view, leaving the base
tables opened at the RSAM level.

'Informix > informix troubleshooting' 카테고리의 다른 글

Slow connect time to Informix when using TCP/IP (0)	2023.10.12
A possible cause for slow connection to the database server (0)	2023.10.12
Where do I start with Diagnosis of Generally Slow Performance on Informix Servers? (0)	2023.01.11
Techniques for troubleshooting problems (0)	2021.06.13
오류 정보 수집이 설정되었는지 확인하는 방법 (0)	2021.06.10

Question

Your IBM Informix server is processing queries more slowly than expected. You want to determine what is slowing the instance. Were do you start your analysis?

Answer

Preconditions

This performance problem is not limited to one query or a class of queries.
The database statistics (UPDATE STATISTICS) were collected with appropriate performance considerations and are current.
The performance is a problem for both network connections to the server and shared memory connections.

Recommended Approach

Informix servers provides a number of commands, the onstats, for reporting system status. You can generally start your analysis using seven of the commands. Examination of the data often leads to further analysis and resolution. The recommended commands are:

onstat -m

Message log. The message log contains warning and error messages. The onstat -m only reports recent updates to the log file. You may want to look at earlier log entries.
onstat -u

User thread status. Look for user threads waiting on a resource. You will see a wait indicator in the first position of the flags field. The address of the resource is in the wait field. The specific wait flag, the type of resource, and cross references follow:

B - wait on buffer - match the wait address to a buffer in onstat -b

C - wait on checkpoint - examine onstat -g ckp and onstat -g con

G - wait on write to log buffer - match the wait address to a log buffer in onstat -l

L - wait on lock - match the wait address to the address of a lock in onstat -k

S - wait on latch - match the wait address to a latch (mutex) in onstat -lmx and onstat -wmx

Y - wait on condition - listed in onstat -g con and do not typically reflect performance problems to the extent that they help with diagnosis

There are several other flags but they are rarely observed.

The first field of the onstat -u output, address, maps to the rstcb field of the onstat -g ath output for thread identification. The sessid field is the session id (SID). You can learn more about resources allocated to a session and its activity with onstat -g ses <SID>.

Collect onstat -u several times in rapid succession. If the waiters persist over a measurable clock time, then the chances are very good that the waits reflect a processing problem that affects performance. Some waiters are normal but they should disappear quickly. Keep in mind that lock waits are programmed.

The last two fields of onstat -u, nreads and nwrites, can be useful indicators of thread activity.
onstat -p

Server statistics. The single most important performance statistic in this output is the read cache rate (the first %cached field). Your goal for an OLTP system is to have a read cache rate above 95 percent during normal processing. Try for a rate above 99 percent. Increase the cache rate by adding buffers, which are configured using the BUFFERPOOL configuration parameter. Make sure that you have plenty of system memory (onstat -g osi) when increasing the Informix server memory allocation. Increasing the server memory footprint can indirectly slow the instance by increasing paging/swapping at the OS side. You can use a sysmaster query (see Related Information below) to help determine if you have configured too many buffers.

Other statistics, like bufwaits (waiting for buffer), seqscans (sequential scans), and the read aheads should be considered. In the case of read aheads, the sum of ixda-RA, idx-RA, and da-RA should be close to the value of RA-pgsused as an indicator of effective read-ahead configuration.

Many of the same statistics are viewed on a partnum level with onstat -g ppf.

Consider collecting and retaining onstat -p outputs at regular intervals for comparison. Note that cumulative statistics are zeroed with onstat -z if you want to observe statistics over a limited time interval.
onstat -g rea

Ready queue. Reports threads ready to execute at one moment in time. These threads are prepared for execution but lack cpu resource. If the number remains above the number of cpu virtual processors (onstat -g glo) through repetitions of onstat -g rea, then your system is likely limited by processing power. Look for inefficient queries and non-server processing on the machine. See onstat -g glo output for cpu use integrated over time and onstat -g osi for system resources.
onstat -g act

Active threads. You will usually see poll threads and listen threads in this queue. Look for threads doing query-related work, like sqlexec and I/O threads. If none show up or they are rare, then work is not getting to the processors.
onstat -g ioq

I/O request queues. The statistic to monitor is the maxlen column. This is the maximum length of a queue after the engine was brought online or onstat -z was executed. If the number is too large, then at some point the I/O requests were not serviced fast enough. Try executing onstat -z and checking to see how long it takes for large maxlen values to return.

For informix aio monitor the gfd (global file descriptor) queues. The maxlen should not be greater than 25. The system uses gfd 0, 1, and 2 (stdin, stdout, stderr), so the informix chunks start with gfd 3 (chunk 1).

If you are using kaio monitor the kio queues. The maxlen values should not exceed 32. There will be one kio queue for each cpu virtual processor.

Recommendations for maxlen with DIRECT_IO enabled have not been determined, but are not expected to be larger than 32.
onstat -g seg

Shared memory allocations. The shared memory configuration parameters should be tuned so that the server dynamically allocates at most two virtual segments during normal processing.

https://www.ibm.com/support/pages/where-do-i-start-diagnosis-generally-slow-performance-informix-servers

Where do I start with Diagnosis of Generally Slow Performance on Informix Servers?

www.ibm.com

'Informix > informix troubleshooting' 카테고리의 다른 글

A possible cause for slow connection to the database server (0)	2023.10.12
104 ISAM error: too many files open. (0)	2023.01.13
Techniques for troubleshooting problems (0)	2021.06.13
오류 정보 수집이 설정되었는지 확인하는 방법 (0)	2021.06.10
Informix 12.10.xC14의 DBINFO 함수 오류 (0)	2020.12.22

Troubleshooting is a systematic approach to solving a problem. The goal of troubleshooting is to determine why something does not work as expected and how to resolve the problem. Certain common techniques can help with the task of troubleshooting.

The first step in the troubleshooting process is to describe the problem completely. Problem descriptions help you and the IBM® technical-support representative know where to start to find the cause of the problem. This step includes asking yourself basic questions:

What are the symptoms of the problem?
Where does the problem occur?
When does the problem occur?
Under which conditions does the problem occur?
Can the problem be reproduced?

The answers to these questions typically lead to a good description of the problem, which can then lead you to a problem resolution.

What are the symptoms of the problem?

When starting to describe a problem, the most obvious question is "What is the problem?" This question might seem straightforward; however, you can break it down into several more-focused questions that create a more descriptive picture of the problem. These questions can include:

Who, or what, is reporting the problem?
What are the error codes and messages?
How does the system fail? For example, is it a loop, hang, crash, performance degradation, or incorrect result?

Where does the problem occur?

Determining where the problem originates is not always easy, but it is one of the most important steps in resolving a problem. Many layers of technology can exist between the reporting and failing components. Networks, disks, and drivers are only a few of the components to consider when you are investigating problems.

The following questions help you to focus on where the problem occurs to isolate the problem layer:

Is the problem specific to one platform or operating system, or is it common across multiple platforms or operating systems?
Is the current environment and configuration supported?
Do all users have the problem?
(For multi-site installations.) Do all sites have the problem?

If one layer reports the problem, the problem does not necessarily originate in that layer. Part of identifying where a problem originates is understanding the environment in which it exists. Take some time to completely describe the problem environment, including the operating system and version, all corresponding software and versions, and hardware information. Confirm that you are running within an environment that is a supported configuration; many problems can be traced back to incompatible levels of software that are not intended to run together or have not been fully tested together.

When does the problem occur?

Develop a detailed timeline of events leading up to a failure, especially for those cases that are one-time occurrences. You can most easily develop a timeline by working backward: Start at the time an error was reported (as precisely as possible, even down to the millisecond), and work backward through the available logs and information. Typically, you need to look only as far as the first suspicious event that you find in a diagnostic log.

To develop a detailed timeline of events, answer these questions:

Does the problem happen only at a certain time of day or night?
How often does the problem happen?
What sequence of events leads up to the time that the problem is reported?
Does the problem happen after an environment change, such as upgrading or installing software or hardware?

Responding to these types of questions can give you a frame of reference in which to investigate the problem.

Under which conditions does the problem occur?

Knowing which systems and applications are running at the time that a problem occurs is an important part of troubleshooting. These questions about your environment can help you to identify the root cause of the problem:

Does the problem always occur when the same task is being performed?
Does a certain sequence of events need to happen for the problem to occur?
Do any other applications fail at the same time?

Answering these types of questions can help you explain the environment in which the problem occurs and correlate any dependencies. Remember that just because multiple problems might have occurred around the same time, the problems are not necessarily related.

Can the problem be reproduced?

From a troubleshooting standpoint, the ideal problem is one that can be reproduced. Typically, when a problem can be reproduced you have a larger set of tools or procedures at your disposal to help you investigate. Consequently, problems that you can reproduce are often easier to debug and solve.

However, problems that you can reproduce can have a disadvantage: If the problem is of significant business impact, you do not want it to recur. If possible, re-create the problem in a test or development environment, which typically offers you more flexibility and control during your investigation.

Can the problem be re-created on a test system?
Are multiple users or applications encountering the same type of problem?
Can the problem be re-created by running a single command, a set of commands, or a particular application?

출처 : https://www.ibm.com/docs/en/informix-servers/12.10?topic=support-techniques-troubleshooting-problems

저작자표시 비영리 변경금지

'Informix > informix troubleshooting' 카테고리의 다른 글

104 ISAM error: too many files open. (0)	2023.01.13
Where do I start with Diagnosis of Generally Slow Performance on Informix Servers? (0)	2023.01.11
오류 정보 수집이 설정되었는지 확인하는 방법 (0)	2021.06.10
Informix 12.10.xC14의 DBINFO 함수 오류 (0)	2020.12.22
View에서 Sequence의 NEXTVAL 값 참조하기 (0)	2019.12.16

얼마전에 고객사 인포믹스에서 243 오류(Could not position within a table table-name)가 발생해서 onmode -I 명령으로 동일한 오류가 발생했을 때 진단 정보를 수집하도록 설정해두었습니다.

그런데 진단 정보를 수집하는 설정 상태를 확인하는 방법이 있는지 알고싶어 IBM Community에 질문 글을 썼습니다.

onmode -I 명령을 수행하면 아래와 같이 인포믹스 온라인 로그에 메시지가 표시되기는 하지만 현재 설정되었는지 여부를 확인하는 방법을 알고 싶었습니다.

11:55:15  Verbose error trapping set, errno = 243, session_id = -1

질문 글을 올리고 금방 답변을 받았는데.. onstat 명령에 숨겨진 옵션이 있더군요.

$ onmode -I 243
$ onstat -g ras
IBM Informix Dynamic Server Version 11.70.FC9 -- On-Line -- Up 01:07:23 -- 23486904 Kbytes
 Diagnostics           Current Settings
 AFCRASH               0x00002481
 AFFAIL                0x00000401
 AFWARN                0x00000401
 AFDEBUG               0
 AFLINES               0
 BLOCKTIMEOUT          3600
 Block Status          Unblocked
 CCFLAGS               0x00000000
 CCFLAGS2              0x00000200
 DUMPCORE              0
 DUMPGCORE             0
 DUMPSHMEM             1
 DUMPCNT               1
 DUMPDIR               /work1/informix/ids1150fc6/tmp
 RHEAD_T               0x00000000541c3800
 SYSALARMPROGRAM       /work1/informix/ids1150fc6/etc/evidence.sh
 LTXHWM                70%
 LTXEHWM               80%
 DYNAMIC_LOGS          2
Traperror info:
 Errno          Session(s)
 243            All

onstat -g ras 명령의 출력 결과입니다. -g ras 옵션은 onstat 도움말과 IBM의 매뉴얼에 설명되지 않은 숨겨진 옵션입니다. 결과의 Traperror info 부분에서 현재 에러번호(Errno)와 특정 세션번호에 대해 진단 정보를 수집하도록 설정되어 있음을 확인할 수 있습니다.

저작자표시 비영리 변경금지

'Informix > informix troubleshooting' 카테고리의 다른 글

Where do I start with Diagnosis of Generally Slow Performance on Informix Servers? (0)	2023.01.11
Techniques for troubleshooting problems (0)	2021.06.13
Informix 12.10.xC14의 DBINFO 함수 오류 (0)	2020.12.22
View에서 Sequence의 NEXTVAL 값 참조하기 (0)	2019.12.16
14.10.xC2 이하 버전에서 restore 불가한 defect (0)	2019.09.27

얼마전에 고객사에 Informix 12.10.FC14버전을 설치했었는데 기존에 실행했던 쉘스크립트가 실행이 안되더군요. 오늘 IBM My Notifications 메일을 보고 관련된 내용을 알게 되어 공유하고자 합니다.

관련된 문서는 아래 링크에서 확인하실 수 있습니다.

www.ibm.com/support/pages/when-invoking-dbinfodbspacepartnum-error-727-raised

DBINFO 함수를 호출할 때 발생하는 문제인데요. partnum 값이 작은 경우 727 오류가 발생합니다.

$ echo "select first 1 dbinfo('dbspace',partnum) from systabnames;" | dbaccess sysmaster
Database selected.
  727: Invalid or NULL TBLspace number given to dbinfo(dbspace).
Error in line 1
Near character position 56
Database closed.

이전까지는 partnum이 가장 작은 값은 1048577이었지만 12.10.xC14 버전부터는 pseudo-tables, 소위 의사 테이블이 몇가지 추가되었다고 합니다. 14버전은 14.10.xC2 부터라는군요. 아래처럼 partnum 값이 6부터 153 까지 몇개 추가되었음을 알 수 있습니다.

$ echo "select first 10 partnum from sysmaster:systabnames;" | dbaccess stores_demo
Database selected.
    partnum
          6
         10
         15
         89
        153
    1048577
    1048578
    1048579
    1048580
    1048582

따라서 DBINFO 사용시 오류를 회피하기 위해서는 partnum 값이 1048576 이상인 값을 대상으로 조회해야 오류가 발생하지 않는다고 합니다. 관리적인 목적으로 DBINFO 함수를 사용하고 있다면 조건을 추가해야겠네요. 제가 테스트한바로는 위에서 보이는 153 이상의 조건으로도 오류가 발생하지는 않지만 IBM에서 권고하는 방법을 사용하는게 좋겠죠.

IBM문서에는 DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM 환경변수를 설정하면 된다고 합니다만 설정 전후 동작에 차이가 없는 것 같습니다. IBM에 별도의 패치버전을 요청해야되나 봅니다.

$ export DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM=1
$ echo "select first 1 dbinfo('dbspace',partnum) from systabnames;" | dbaccess sysmaster
Database selected.
  727: Invalid or NULL TBLspace number given to dbinfo(dbspace).
Error in line 1
Near character position 56
Database closed.
$ dbaccess sysmaster -
Database selected.
> set environment DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM "1";
19840: Invalid session environment variable.
Error in line 1
Near character position 65
>

12.10.xC14 버전 이상을 사용하실 때 참고하시면 좋겠습니다.

저작자표시 비영리 변경금지

'Informix > informix troubleshooting' 카테고리의 다른 글

Techniques for troubleshooting problems (0)	2021.06.13
오류 정보 수집이 설정되었는지 확인하는 방법 (0)	2021.06.10
View에서 Sequence의 NEXTVAL 값 참조하기 (0)	2019.12.16
14.10.xC2 이하 버전에서 restore 불가한 defect (0)	2019.09.27
Informix fails with exception "java.sql.SQLException: Blob not found" (0)	2019.09.04

안녕하세요. 회사 업무로 원격지에 있는 DB서버의 SEQUENCE값을 가져오는 방법에 대해 조사해보았습니다.

직접 쿼리로 수행하는 것보다는 VIEW를 사용하는 방법을 먼저 시도해보았는데요.

아래와 같이 8319 오류가 발생했습니다.

8319 오류메시지를 finderr 명령으로 살펴보면 VIEW 정의에서는 CURRVAL 또는 NEXTVAL 키워드를 사용할 수 없다고 나와있습니다.

Db2와 Oracle에서도 동일한 제약사항이 있었습니다. 어쨌든 이런 현상을 우회하기 위해 사용자 정의 함수를 생성하고 해당 함수를 호출하는 VIEW를 생성해보았습니다. VIEW가 잘 생성되고 조회도 아래와 같이 잘 수행되었습니다.

RDBMS의 ANSI 표준인지 명확히 어떤 이유로 직접 VIEW 정의에서 사용할 수 없는지는 모르겠습니다.

이유를 알게 되면 업데이트하겠습니다.

참조 : https://www.jamescoyle.net/how-to/2872-oracle-ora-02287-sequence-number-not-allowed-here

Oracle ORA-02287: sequence number not allowed here | JamesCoyle.net Limited

I've recently hit an issue when trying to include a sequence.nextval in an Oracle database view. the database throws the following error: ORA-02287: sequence number not allowed here I can see why

www.jamescoyle.net

'Informix > informix troubleshooting' 카테고리의 다른 글

오류 정보 수집이 설정되었는지 확인하는 방법 (0)	2021.06.10
Informix 12.10.xC14의 DBINFO 함수 오류 (0)	2020.12.22
14.10.xC2 이하 버전에서 restore 불가한 defect (0)	2019.09.27
Informix fails with exception "java.sql.SQLException: Blob not found" (0)	2019.09.04
VARCHAR 컬럼 사이즈 축소후 UPDATE 실행으로 인한 인포믹스 다운 (0)	2019.03.20

안녕하세요. 14.10.xC1 과 14.10.xC2 버전의 defect를 알려드립니다. 복구가 안되는 심각한 문제입니다.

Restore with on-Bar using the Veritas™ NetBackup™ storage manager is not possible using Informix database server versions 14.10.xC1 and 14.10xC2.

Abstract

The retrieval of objects for a restore operation is not possible using certain storage managers, such as Veritas™ NetBackup™. The XBSA call BSAGetObject() fails with error 159 (0x9f).

Content

This behavior is documented as a defect in the following APAR:

IT30316 RESTORE FUNCTIONALITY IS NOT WORKING WHEN USING NETBACKUP STORAGE MANAGER

At the time of this writing, the APAR fix is scheduled in Informix database server version 14.10.xC3. To determine whether and when the APAR is fixed and in what version it is fixed, monitor the APAR link.

If you use Veritas™ NetBackup™ with Informix database server versions 14.10.xC1 and 14.10.xC2, you need a fix for this APAR to successfully restore an Informix database server instance. When the fix is available, customers at risk can request a special build that includes the fix or they can upgrade to the interim release that contains the fix when it becomes available.

The only known workarounds are:

Do not use on-Bar to archive and restore, use ontape or external archives and restores instead.
Use a storage manager other than Veritas™ NetBackup™ (that is, Informix Primary Storage Manager).

To confirm encountering this defect, set the onCONFIG configuration file parameter BAR_DEBUG to any number 1 - 9 and rerun the on-Bar restore. This setting turns on extra logging in the BAR_DEBUG_LOG file. If the following signature is encountered, the defect is confirmed:

BSAGetObject: input: bufferLen = 63488, numBytes = 0
...
BSAGetObject: output: bufferLen = 63488, numBytes = 0
BSAGetObject: return 0 (0x00)
...
barGetObjectMain: return 0 (0x00)
barGetObject: BUG!!, numbtes(0) < headerlen(4096).
barGetObject: return 159 (0x9f)

조만간 임시 수정버전이 공개될 것 같습니다. 참고되시길~

https://www.ibm.com/support/pages/node/1074446?myns=swgimgmt&mynp=OCSSGU8G&mync=E&cm_sp=swgimgmt-_-OCSSGU8G-_-E

'Informix > informix troubleshooting' 카테고리의 다른 글

Informix 12.10.xC14의 DBINFO 함수 오류 (0)	2020.12.22
View에서 Sequence의 NEXTVAL 값 참조하기 (0)	2019.12.16
Informix fails with exception "java.sql.SQLException: Blob not found" (0)	2019.09.04
VARCHAR 컬럼 사이즈 축소후 UPDATE 실행으로 인한 인포믹스 다운 (0)	2019.03.20
syssessions 테이블에서 CURRENT 세션 정보가 표시되지 않는 문제 (0)	2019.02.11

Informix/informix troubleshooting

Troubleshooting

Problem

Symptom

Cause

Resolving The Problem

'Informix > informix troubleshooting' 카테고리의 다른 글

Troubleshooting

Problem

Cause

Diagnosing The Problem

Resolving The Problem

'Informix > informix troubleshooting' 카테고리의 다른 글

Troubleshooting

Problem

Symptom

Cause

Diagnosing The Problem

Resolving The Problem

'Informix > informix troubleshooting' 카테고리의 다른 글

Cause

Diagnosing The Problem

Resolving The Problem

'Informix > informix troubleshooting' 카테고리의 다른 글

Question

Answer

'Informix > informix troubleshooting' 카테고리의 다른 글

What are the symptoms of the problem?

Where does the problem occur?

When does the problem occur?

Under which conditions does the problem occur?

Can the problem be reproduced?

'Informix > informix troubleshooting' 카테고리의 다른 글

'Informix > informix troubleshooting' 카테고리의 다른 글

'Informix > informix troubleshooting' 카테고리의 다른 글

'Informix > informix troubleshooting' 카테고리의 다른 글

Restore with on-Bar using the Veritas™ NetBackup™ storage manager is not possible using Informix database server versions 14.10.xC1 and 14.10xC2.

Abstract

Content

'Informix > informix troubleshooting' 카테고리의 다른 글

티스토리툴바