Save 
Join IIUG
 for   
 

RSS Feed: IDS Forum: IDS

More IIUG RSS Feeds

The IDS Forum is a discussion forum to communicate about all aspects of the Informix Dynamic Server (IDS) Engine


URL: http://www.iiug.org/rss/ids.rss

Below is the latest content available from this feed:

Re: dbexport/dbimport question
Posted by: david.grove@alaska.gov (DAVID GROVE) - Thu, 25 Apr 2019 13:44:57 EDT
[This is a sidebar rant]



"Firstly I should say that dbimport and dbexport are not backup tools although

I suppose they can be used as such for a small instance than can stand

downtime and for which you can rebuild an instance quickly and efficiently.

You should really look at using onbar."



Database backup is a issue that has annoyed us for many years: Other than

dbexport/dbimport, Informix does not provide a general means to backup a

database. (The various tools provided have limitations that prohibit their use

in backing up a single database: some cannot backup a database that contains

large objects; and, some can backup only at the instance or dbspace level.

None [except dbexport/dbimport] exactly backup a database.)



We have asked several times over the past 20 years. Always the same answer:

Informix is designed NOT to be able to do backups at the database level.



Maybe we've not asked clearly; or, maybe we've misunderstood the answer-- If

so, I apologize for the false assertion, and would pay money to be set

straight!



I want to be able to backup a database just like ontape backs up an instance--

fast, and without needing exclusive access.



DG









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



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: [41633]



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

Re: dbexport/dbimport question
Posted by: benjamin.thompson@skybettingandgaming.com (BENJAMIN THOMPSON) - Wed, 24 Apr 2019 11:52:27 EDT
Hi,



We are running informix 12.10 FC8 and setting up a dbexport/dbimport for

database backups would like to ask th following.



1. Does dbexport includes the dbspace where the db resides? We have multiple

dbs that are created on different dbspaces.



2. If #1 doesn't include the dbspace of the db then how can I query the

dbspace where my db was created? I know it can be viewed using "dbaccess

dbname" but is there a query to view this detail?



3. For example I have dbspace1, dbspace2 and dbspace3 if I use dbimport and

not specify the dbspace (-d) where would the database be created?



Thanks and more power to IIUG



Hi,



Firstly I should say that dbimport and dbexport are not backup tools although

I suppose they can be used as such for a small instance than can stand

downtime and for which you can rebuild an instance quickly and efficiently.

You should really look at using onbar.



dbexport includes where tables and indices are stored if the storage schema

option, "-ss", is specified. However if the object resides in the default

dbspace for its database and no storage option was specified at creation

"dbexport -ss" may not include an "in <dbspace>" clause.



Can I suggest you look at the dbexport documentation:

https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.mig.doc/ids_mig_127.htm



The answer to your second question is there:



-d dbspace

Specifies the dbspace where the database is created.

If this is omitted, the default location is the root dbspace



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: [41632]



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

Re: Creation on read only user.
Posted by: benjamin.thompson@skybettingandgaming.com (BENJAMIN THOMPSON) - Wed, 24 Apr 2019 11:42:20 EDT
I think there is a slight bit of confusion in your question about how the

privileges work:



"'USER1' and 'USER2' can Execute SELECT, INSERT, UPDATE, and DELETE statements

as they gave got 'CONNECT' database level privilege, that comes with default

'PUBLIC' role."



1. By default databases (at least non-ANSI ones) have GRANT CONNECT TO PUBLIC:

this allows creation of a session plus select access on some/most system

tables with id numbers < 100, e.g. queries like "select tabname from

systables;".



2. For each individual table the engine will automatically grant SELECT,

INSERT, UPDATE, and DELETE privileges to PUBLIC if NODEFDAC was not set in the

environment of the user who created the table at the time it was created.



While these allow someone to get started quickly without concerning themselves

with granting privileges to users or groups, it does mean that if you began

this way you're going to have to unpick it to implement something more secure.



I recommend using NODEFDAC to prevent automatic granting of privileges but it

is an environment variable and not a server parameter so you need complete

control of your environment.



This aside, to create your read-only user without affecting the other two

users you're going to have to revoke at least the privileges granted to PUBLIC

you don't want everyone to have and replace them with something else more

targetted. One way of doing it would be to create a role, grant the privileges

to this role and then make the role the default role for the users. Revoke the

privileges from public the role provides.



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: [41631]



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

dbspace encryption and upgrade IFMX in-place
Posted by: eric.vercelletto@begooden-it.com (ERIC VERCELLETTO) - Wed, 24 Apr 2019 11:34:52 EDT
Hi,



did anyone experience this migration path:

12.10 FC10 to 14.10

WITH ENCRYPTED DBSPACES



I have seen nothing in the migration guide on the IBM site regarding

ENCRYPTION requirements.



When my customer launches oninit in the new 14.10 environment, he gets and

assert failed clearly when trying to read something in a dbspace, and

everything stops there



It works correctly on the same environment if there is not dbspace encryption.



Is this a missing point in the documentation that should say that dbspaces

must not be encrypted, or anything else?



Any experience appreciated



Thanks

Eric









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



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: [41630]



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

Re: dbexport failure
Posted by: eric.vercelletto@begooden-it.com (ERIC VERCELLETTO) - Wed, 24 Apr 2019 11:19:27 EDT
Why don't you ttry John Miller's 'export' stored procedure?



It will work on any informix above 11.50 xC3 ( that is having external tables)









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



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: [41629]



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

Re: SQLServer to Informix conversion
Posted by: eric.vercelletto@begooden-it.com (ERIC VERCELLETTO) - Wed, 24 Apr 2019 11:15:08 EDT
I know a tool that can handle a migration from MySQL to IFMX, and apparently

it does work (according to the doc) with MS SQL Server.



The tools is called IBM Migration Toolkit.



What I know is that the product is no more maintained by IBM, not sure about

what type of license it has.



There is also a red book on migrating MS SQL Server to IFMX that you can find

in the IBM publications.









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



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: [41628]



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

Re: Limit User Connections
Posted by: eric.vercelletto@begooden-it.com (ERIC VERCELLETTO) - Wed, 24 Apr 2019 11:04:21 EDT
Brilliant, just set the limit higher than 3 *lol*



sysdbopen procedure allows to do tons of really nice things!









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



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: [41627]



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

Re: EXTERNAL table read
Posted by: eric.vercelletto@begooden-it.com (ERIC VERCELLETTO) - Wed, 24 Apr 2019 11:01:45 EDT
Sergio,

an external table is seen as a table in IDS, the difference is that it is no

installed in the dbspace but in files out of the dbspaces.



You can use any SELECT statement even with where clauses, but you are not

entitled to create indexes for an external table.



you can INSERT INTO an external table, but it has to be the full file or

directory contents you declared in the table schema. YOu cannot INSERT rows

one by one.



You cannot UPDATE no DELETE rows from an external table.



Having indexes on external tables would be a good thing when you handle

enormous files :-)



Something to consider: use of external tables is way faster that load and

unload SQL statements. This is true for unloading data (INSERT INTO ext_table

SELECT xxx xxx FROM real_table), and also for importing data (INSERT INTO

real_table SELECT *** (WHERE xxx) from ext_table.









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



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: [41626]



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

Creation on read only user.
Posted by: mailtodeepakpatel@gmail.com (DEEPAK PATEL) - Wed, 24 Apr 2019 10:57:27 EDT
Hello,



1. I have 2 user in my instance i.e. 'USER1' and 'USER2'.



2. 'USER1' and 'USER2' can Execute SELECT, INSERT, UPDATE, and DELETE

statements as they gave got 'CONNECT' database level privilege, that comes

with default 'PUBLIC' role.



3. I want to create 'USER3' that should be READONLY user. How this can be

achieved without affecting ‘USER1’ and ‘USER2’



Please help with this issue. 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: [41625]



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

Re: EXTERNAL table read
Posted by: luis-silvestre-marques@telecom.pt (LUIS MARQUES) - Tue, 23 Apr 2019 03:09:39 EDT
An EXTERNAL table is a permanent object in the database. Other sessions, if

they have the proper permissions, can read and insert into it. Every insert

into the external table will truncate the table ( it will truncate the files

that support the external table ) and replace it's contents.

Check the online documentation. There are several examples that might be

helpful:



https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_2068.htm









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



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: [41624]



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

dbexport/dbimport question
Posted by: lpolicarpio2009@yahoo.com (LESTER POLICARPIO) - Mon, 22 Apr 2019 21:41:41 EDT
Hi,



We are running informix 12.10 FC8 and setting up a dbexport/dbimport for

database backups would like to ask th following.



1. Does dbexport includes the dbspace where the db resides? We have multiple

dbs that are created on different dbspaces.



2. If #1 doesn't include the dbspace of the db then how can I query the

dbspace where my db was created? I know it can be viewed using "dbaccess

dbname" but is there a query to view this detail?



3. For example I have dbspace1, dbspace2 and dbspace3 if I use dbimport and

not specify the dbspace (-d) where would the database be created?



Thanks and more power to IIUG









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



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: [41623]



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

Re: Limit User Connections
Posted by: douglawry@hotmail.com (DOUG LAWRY) - Mon, 22 Apr 2019 13:27:05 EDT
Hi Dave.



I see this never had a response. Here is one approach:



CREATE PROCEDURE public.sysdbopen()



DEFINE session_count SMALLINT;



IF USER != 'informix' THEN



SELECT COUNT(*)



INTO session_count



FROM sysmaster:syssessions



WHERE username = USER;



IF session_count > 3 THEN -- set limit here



RAISE EXCEPTION -746, 0, 'Maximum sessions exceeded';



END IF



END IF



END PROCEDURE;



Regards,

Doug Lawry









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



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: [41622]



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

12.10->14.10 with EaR
Posted by: andi@mega.donetsk.ua (ANDY IGNATOV) - Mon, 22 Apr 2019 08:06:34 EDT
Hi

I have a problem with migration from 12.10 to 14.10 with encryption at rest



Main steps are:

• I have Informix 12.10FC12 with “DISK_ENCRYPTION” parameter

• Install Informix 14.10 in the different directory (install gsk8_64 too). Set

correct INFROMIXDIR...

• Copy corresponding files to INFROMIXDIR/etc (from previous, 12.10

installation), such as: onconfig, sqlhosts, keystore.p12, keystore.sth…

• Run Informix instance:

oninit



Log file content:

....

04/18/19 04:47:09 Booting Language <c> from module <>

04/18/19 04:47:09 Loading Module <CNULL>

04/18/19 04:47:09 Booting Language <builtin> from module <>

04/18/19 04:47:09 Loading Module <BUILTINNULL>

04/18/19 04:47:15 DR: DRAUTO is 0 (Off)

04/18/19 04:47:15 DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)

04/18/19 04:47:15 Event notification facility epoll enabled.

04/18/19 04:47:15 Entries in the surrogates file

/etc/informix/allowed.surrogates are loaded into surrogate cache.

04/18/19 04:47:15 Trusted host cache successfully built:...

04/18/19 04:47:15 CCFLAGS2 value set to 0x200

04/18/19 04:47:15 SQL_FEAT_CTRL value set to 0x8008

04/18/19 04:47:15 SQL_DEF_CTRL value set to 0x4b0

04/18/19 04:47:15 IBM Informix Dynamic Server Version 14.10.FC1

04/18/19 04:47:16 Conversion from version 12.10.xC8 Started

04/18/19 04:47:16 Assert Failed: Page Check Error in init_dbspace_pre_chunk

04/18/19 04:47:16 IBM Informix Dynamic Server Version 14.10.FC1

04/18/19 04:47:16 Who: Session(1, informix@posproregister, 0, 0x4592e028)



Thread(7, main_loop(), 458e6028, 1)



File: rsdebug.c Line: 982

04/18/19 04:47:16 Results: Possible inconsistencies in '(null)'

....

04/18/19 04:47:23 Page Check Error in init_dbspace_pre_chunk

04/18/19 04:47:23 oninit: Fatal error in shared memory initialization



• The similar issue with restoring from ontape backup

• By the way, I see no error about encrypted restore while “DISK_ENCRYPTION”’s

files exit. On 12.10 it looks like:



One or both of the Encryption Key Database



(/opt/IBM/informix/etc/keystore.p12)



or Encryption Key Stashfile



(/opt/IBM/informix/etc/keystore.sth)



already exists. Because this is a complete initialization of



the __INF__ server they must not already exist.



Remove the file(s) manually or set the FULL_DISK_INIT onconfig



parameter to remove the files automatically.

Creation of encryption key database failed: GSKKM_ERR_DATABASE_ALREADY_EXISTS

(/opt/IBM/informix/etc/keystore.p12)

Physical restore failed - InitKeystore failed



• I have no errors when I use unencrypted dbspaces, everything works fine...



What's wrong with my installation? Thank to all for any ideas...









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



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: [41621]



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

Resolved: dbexport failure
Posted by: david.grove@alaska.gov (DAVID GROVE) - Fri, 19 Apr 2019 13:45:35 EDT
Note I say, "resolved", not "solved".



I just don't have time to work all the details. This was a side-bar to the

process of getting myexport to work properly in our system, which, itself, was

a sidebar to another task.



I feel like I have encountered stack overflow!



Anyway, the "fix" was to CAST to INT the SERIAL column in the outer-most

SELECT of the complex, 500+ line, single SQL query ("CREATE TABLE AS... ")

that was causing the problem.



Now the column produced in the target table by the "CREATE TABLE AS..."

statement is an INT, and the NULLs are OK.



dbexport now runs to successful completion.



Why Informix executes a "CREATE TABLE AS... " statement which creates a column

that is SERIAL and also has NULLs, without terminating and generating an error

message is still a mystery, and beyond me. But, then, a lot of things are

above my pay grade!



Did not open a case on this, after all. A matter of available time.



Regards,



DG









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



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: [41620]



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

Re: EXTERNAL table read
Posted by: sergio.peres@airc.pt (SERGIO PERES) - Thu, 18 Apr 2019 21:12:57 EDT
Thanks for your reply,



I itend to use it as temporary unload for query and after read it as a normal

table, but seems that I can't do it! Is this correct?









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



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: [41619]



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

Re: EXTERNAL table read
Posted by: sergio.peres@airc.pt (SERGIO PERES) - Thu, 18 Apr 2019 21:12:51 EDT
Thanks for your reply,



I itend to use it as temporary unload for query and after read it as a normal

table, but seems that I can't do it! Is this correct?









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



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: [41618]



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

Re: EXTERNAL table read
Posted by: luis-silvestre-marques@telecom.pt (LUIS MARQUES) - Thu, 18 Apr 2019 04:27:25 EDT
There are restrictions to operations can be done on EXTERNAL TABLES. Check the

online documentation for more details:





https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_2071.htm



You can define an EXTERNAL TABLE and then have an external program modify the

data file . For example, you can define an EXTERNAL TABLE once for a daily

bulk load, and everyday you smash the table data file with a new content. It

is one of the ways to load unload data into the database from flat files.









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



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: [41617]



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

Informix 11.70: Stored Procedure SQL exceptions
Posted by: bob.kruse@marriott.com (BOB KRUSE) - Wed, 17 Apr 2019 19:44:31 EDT
Greetings,



I've written an extensive stored procedure that has some dynamic SQL. I have

an exception handler defined with the "With Resume" clause to continue

processing if an error occurs. The trouble is, when the code is in a FOR loop

and incurs an error, it drops out of the loop. I tried defining the for loop

as a cursor with hold but that did not help.



Any suggestions?



Is this managed better in 14.10?



Thanks,

Bob Kruse









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



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: [41616]



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

EXTERNAL table read
Posted by: sergio.peres@airc.pt (SERGIO PERES) - Wed, 17 Apr 2019 19:02:08 EDT
Hi,



How can I read one external table as an usual table, is it possible?

Can I do select from one external table created by another program?

As I only have seen operations over external table after create...???



Thanks for any help,



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: [41615]



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

Re: Log table change dbspace
Posted by: sergio.peres@airc.pt (SERGIO PERES) - Wed, 17 Apr 2019 18:57:36 EDT
Hi Alexandre,



Thanks for your reply, I will take a look to your advices.

We are doing some tests as we are facing some problems over some customers

that have the highest level log selected.



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: [41614]



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

Hi Alexandre,



Thanks for your reply, I will take a look to your advices.

We are doing some tests as we are facing some problems over some customers

that have the highest level log selected.



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: [41614]



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

 
Subscribe to this feed
You can subscribe to this RSS feed in a number of ways, including the following:

Drag the orange RSS button into your News Reader

Drag the URL of the RSS feed into your News Reader

Cut and paste the URL of the RSS feed into your News Reader v