Can't create table or database but no error shows

Hello, I have problem with our informix installation (on Windows 2008 R2 machine). When I try to create table inside existing database (by SQL query) nothing happends When I try to create database in existing dbspace (bySQL query) nothing happedns By "nothing happends" i mean that dbaccess freezes with text "Loading ...", OAT need is executing query (and need to be restarted), razorSQL is executing query but never stops. this command : SELECT dbinfo('dbspace', partnum) FROM systables WHERE tabid = 1; returns: (expression) rootdbs onstat -d returns: Chunks address chunk/dbs offset size free bpages flags pathname 00000000831CA1E0 1 1 0 75000 51596 PO-B-D C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\rootdbs.000 000000008393D1E0 2 2 0 12544 203 PO-B-D C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\plogdbs.000 000000008393E030 3 3 0 15616 203 PO-B-D C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\llogdbs.000 000000008393F030 4 4 0 13312 13259 PO-B-D C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\datadbs.000 0000000083940030 5 5 0 8192 7576 7576 POSB-D C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\sbspace.000 Metadata 563 358 563 0000000083941030 6 6 0 13312 13209 PO-B-D C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\tempdbs.000 6 active, 32766 maximum Expanded chunk capacity mode: always There is nothing in logs Do you have any idea what is going on here? Please help.

We need more information. Please post the following: I see that you are running on Windows, what is your Informix release? Post the following output (including the headlines!): onstat -l onstat -m onstat -g sql onstat -g act Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Oct 1, 2013 at 2:01 PM, DAVID HONZáTKO <dawyd.d@gmail.com> wrote: > Hello, > I have problem with our informix installation (on Windows 2008 R2 machine). > > When I try to create table inside existing database (by SQL query) nothing > happends > When I try to create database in existing dbspace (bySQL query) nothing > happedns > > By "nothing happends" i mean that dbaccess freezes with text "Loading ...", > OAT need is executing query (and need to be restarted), razorSQL is > executing > query but never stops. > > this command : > > SELECT dbinfo('dbspace', partnum) FROM systables WHERE tabid = 1; > returns: > > (expression) rootdbs > > onstat -d returns:> > Chunks > address chunk/dbs offset size free bpages flags pathname > 00000000831CA1E0 1 1 0 75000 51596 PO-B-D > C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\rootdbs.000 > 000000008393D1E0 2 2 0 12544 203 PO-B-D > C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\plogdbs.000 > 000000008393E030 3 3 0 15616 203 PO-B-D > C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\llogdbs.000 > 000000008393F030 4 4 0 13312 13259 PO-B-D > C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\datadbs.000 > 0000000083940030 5 5 0 8192 7576 7576 POSB-D > C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\sbspace.000 > > Metadata 563 358 563 > 0000000083941030 6 6 0 13312 13209 PO-B-D > C:\\\\PROGRA~1\\\\IBMINF~1\\\\OL_INF~2\\\\dbspaces\\\\tempdbs.000 > 6 active, 32766 maximum > > Expanded chunk capacity mode: always > > There is nothing in logs > > Do you have any idea what is going on here? Please help. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3eee4086d8f04e7b2cab7

Thank you Art for your quick response. Here are outputs you have asked for. I hope you can help me. ------------------------------------------------------- onstat -g sql ------------------------------------------------------- IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:03:52 --80384 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 94 CREATE TABLE aropdat_i CR Not Wait 0 0 9.240 Off 90 CREATE DATABAS testdb3 CR Not Wait 0 0 9.240 Off 85 CREATE DATABAS aropdat_i CR Not Wait 0 0 9.240 Off 84 CREATE DATABAS aropdat2_i CR Not Wait 0 0 9.240 Off 71 CREATE TABLE aropdat_i CR Not Wait 0 0 9.240 Off 70 CREATE TABLE aropdat_i CR Not Wait 0 0 4.10. Off 69 - sysmaster CR Not Wait 0 0 4.10. Off 39 CREATE TABLE aropdat_i CR Not Wait 0 0 4.10. Off 35 UPDATE STATIST aropdat_i CR Wait 5 0 0 - Off 29 sysadmin DR Wait 5 0 0 - Off 28 sysadmin DR Wait 5 0 0 - Off 27 UPDATE sysadmin DR Wait 5 0 0 - Off ------------------------------------------------------- onstat -g act ------------------------------------------------------- IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:04:04 --80384 Kbytes Running threads: tid tcb rstcb prty status vp-class name 8 83615030 0 1 running 8soc* soctcppoll 9 836490e0 0 1 running 9soc* soctcpio ------------------------------------------------------- onstat -l ------------------------------------------------------- IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:03:37 --80384 Kbytes Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-2 7 32 616 210 2.93 phybegin physize phypos phyused %used 2:53 12288 7547 10 0.08 Logical Logging Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-3 0 16 28049 5000 4643 5.6 1.1 Subsystem numrecs Log Space used OLDRSAM 27813 4025992 HA 236 10384 Buffer Waiting Buffer ioproc flags L-3 0 0x21 0 address number flags uniqid begin size used %used 00000000831CADB0 1 U------ 13 1:12763 2500 2500 100.00 00000000831CAE20 2 U---C-L 14 1:15263 2500 2499 99.96 00000000831CAE90 3 U------ 3 1:17763 2500 2266 90.64 00000000831CAF00 4 U------ 4 1:20263 2500 3 0.12 00000000831CAF70 5 U------ 5 1:22763 2500 3 0.12 00000000832D7460 6 U------ 6 1:25263 2500 3 0.12 00000000832D74D0 7 U------ 7 3:53 2560 3 0.12 00000000832D7540 8 U------ 8 3:2613 2560 3 0.12 00000000832D75B0 9 U------ 9 3:5173 2560 2560 100.00 00000000832D7620 10 U------ 10 3:7733 2560 2560 100.00 00000000832D7690 11 U------ 11 3:10293 2560 2560 100.00 00000000832D7700 12 U------ 12 3:12853 2560 2560 100.00 12 active, 12 total --------------------------------------------------------- onstat -m --------------------------------------------------------- IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:03:42 --80384 Kbytes Message Log File: C:\\\\PROGRA~1\\\\IBMINF~1\\\\ol_informix1210_1.log 18:18:04 Checkpoint Statistics - Avg. Txn Block Time 0.010, # Txns blocked 1, Plog used 3, Llog used 9 18:18:05 Checkpoint Completed: duration was 0 seconds. 18:18:05 Tue Oct 01 - loguniq 14, logpos 0x9bc018, timestamp: 0x7e3c5 Interval: 699 18:18:05 Maximum server connections 0 18:18:05 Checkpoint Statistics - Avg. Txn Block Time 0.010, # Txns blocked 1, Plog used 3, Llog used 10 18:18:06 Logical Log Files are Full -- Backup is Needed 18:18:06 Checkpoint Completed: duration was 0 seconds. 18:18:06 Tue Oct 01 - loguniq 14, logpos 0x9c2018, timestamp: 0x7e3e2 Interval: 700 18:18:06 Maximum server connections 0 18:18:06 Checkpoint Statistics - Avg. Txn Block Time 0.010, # Txns blocked 1, Plog used 3, Llog used 6 18:37:26 listener-thread: err = -951: oserr = 0: errstr = informix@ARSIQA-64: Incorrect password or user informix@ARSIQA-64 is not known on the database server. 19:20:15 listener-thread: err = -951: oserr = 0: errstr = informix@ARSIQA-64: Incorrect password or user informix@ARSIQA-64 is not known on the database server. That event on 18:18:06 i handled by ontape -a.

18:18:06 Logical Log Files are Full -- Backup is Needed Regards On Tue, Oct 1, 2013 at 9:22 PM, DAVID HONZáTKO <dawyd.d@gmail.com> wrote: > Thank you Art for your quick response. > Here are outputs you have asked for. > > I hope you can help me. > > ------------------------------------------------------- > onstat -g sql > ------------------------------------------------------- > IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:03:52 > --> 80384 Kbytes > > Sess SQL Current Iso Lock SQL ISAM F.E. > Id Stmt type Database Lvl Mode ERR ERR Vers Explain > 94 CREATE TABLE aropdat_i CR Not Wait 0 0 9.240 Off > 90 CREATE DATABAS testdb3 CR Not Wait 0 0 9.240 Off > 85 CREATE DATABAS aropdat_i CR Not Wait 0 0 9.240 Off > 84 CREATE DATABAS aropdat2_i CR Not Wait 0 0 9.240 Off > 71 CREATE TABLE aropdat_i CR Not Wait 0 0 9.240 Off > 70 CREATE TABLE aropdat_i CR Not Wait 0 0 4.10. Off > 69 - sysmaster CR Not Wait 0 0 4.10. Off > 39 CREATE TABLE aropdat_i CR Not Wait 0 0 4.10. Off > 35 UPDATE STATIST aropdat_i CR Wait 5 0 0 - Off > 29 sysadmin DR Wait 5 0 0 - Off > 28 sysadmin DR Wait 5 0 0 - Off > 27 UPDATE sysadmin DR Wait 5 0 0 - Off > > ------------------------------------------------------- > onstat -g act > ------------------------------------------------------- > IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:04:04 > --> 80384 Kbytes > > Running threads: > tid tcb rstcb prty status vp-class name > 8 83615030 0 1 running 8soc* soctcppoll > 9 836490e0 0 1 running 9soc* soctcpio > > ------------------------------------------------------- > onstat -l > ------------------------------------------------------- > IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:03:37 > --> 80384 Kbytes > > Physical Logging > Buffer bufused bufsize numpages numwrits pages/io > P-2 7 32 616 210 2.93 > > phybegin physize phypos phyused %used > > 2:53 12288 7547 10 0.08 > > Logical Logging > Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io > L-3 0 16 28049 5000 4643 5.6 1.1 > > Subsystem numrecs Log Space used > > OLDRSAM 27813 4025992 > > HA 236 10384 > > Buffer Waiting > Buffer ioproc flags > L-3 0 0x21 0 > > address number flags uniqid begin size used %used > 00000000831CADB0 1 U------ 13 1:12763 2500 2500 100.00 > 00000000831CAE20 2 U---C-L 14 1:15263 2500 2499 99.96 > 00000000831CAE90 3 U------ 3 1:17763 2500 2266 90.64 > 00000000831CAF00 4 U------ 4 1:20263 2500 3 0.12 > 00000000831CAF70 5 U------ 5 1:22763 2500 3 0.12 > 00000000832D7460 6 U------ 6 1:25263 2500 3 0.12 > 00000000832D74D0 7 U------ 7 3:53 2560 3 0.12 > 00000000832D7540 8 U------ 8 3:2613 2560 3 0.12 > 00000000832D75B0 9 U------ 9 3:5173 2560 2560 100.00 > 00000000832D7620 10 U------ 10 3:7733 2560 2560 100.00 > 00000000832D7690 11 U------ 11 3:10293 2560 2560 100.00 > 00000000832D7700 12 U------ 12 3:12853 2560 2560 100.00 > 12 active, 12 total > > --------------------------------------------------------- > onstat -m > --------------------------------------------------------- > IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:03:42 > --> 80384 Kbytes > > Message Log File: C:\\\\PROGRA~1\\\\IBMINF~1\\\\ol_informix1210_1.log > 18:18:04 Checkpoint Statistics - Avg. Txn Block Time 0.010, # Txns blocked > 1, > Plog used 3, Llog used 9 > > 18:18:05 Checkpoint Completed: duration was 0 seconds. > 18:18:05 Tue Oct 01 - loguniq 14, logpos 0x9bc018, timestamp: 0x7e3c5 > Interval: 699 > > 18:18:05 Maximum server connections 0 > 18:18:05 Checkpoint Statistics - Avg. Txn Block Time 0.010, # Txns blocked > 1, > Plog used 3, Llog used 10 > > 18:18:06 Logical Log Files are Full -- Backup is Needed > > 18:18:06 Checkpoint Completed: duration was 0 seconds. > 18:18:06 Tue Oct 01 - loguniq 14, logpos 0x9c2018, timestamp: 0x7e3e2 > Interval: 700 > > 18:18:06 Maximum server connections 0 > 18:18:06 Checkpoint Statistics - Avg. Txn Block Time 0.010, # Txns blocked > 1, > Plog used 3, Llog used 6 > > 18:37:26 listener-thread: err = -951: oserr = 0: errstr = > informix@ARSIQA-64: > Incorrect password or user informix@ARSIQA-64 is not known on the database > server. > > 19:20:15 listener-thread: err = -951: oserr = 0: errstr = > informix@ARSIQA-64: > Incorrect password or user informix@ARSIQA-64 is not known on the database > server. > > That event on 18:18:06 i handled by ontape -a. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11339706dd4c5604e7b3dcc8

All of your logical logs have been used and not archived so they cannot be reused. That will cause the engine to block any operation that needs to modify data or the DDL. So, all of those create database, create table, and update statistics commands are hung waiting for you to back up your logical logs! To backup the logs run: ontape -a -or- onbar -b -l Depending on which you use for server archiving. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Oct 1, 2013 at 4:22 PM, DAVID HONZáTKO <dawyd.d@gmail.com> wrote: > Thank you Art for your quick response. > Here are outputs you have asked for. > > I hope you can help me. > > ------------------------------------------------------- > onstat -g sql > ------------------------------------------------------- > IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:03:52 > --> 80384 Kbytes > > Sess SQL Current Iso Lock SQL ISAM F.E. > Id Stmt type Database Lvl Mode ERR ERR Vers Explain > 94 CREATE TABLE aropdat_i CR Not Wait 0 0 9.240 Off > 90 CREATE DATABAS testdb3 CR Not Wait 0 0 9.240 Off > 85 CREATE DATABAS aropdat_i CR Not Wait 0 0 9.240 Off > 84 CREATE DATABAS aropdat2_i CR Not Wait 0 0 9.240 Off > 71 CREATE TABLE aropdat_i CR Not Wait 0 0 9.240 Off > 70 CREATE TABLE aropdat_i CR Not Wait 0 0 4.10. Off > 69 - sysmaster CR Not Wait 0 0 4.10. Off > 39 CREATE TABLE aropdat_i CR Not Wait 0 0 4.10. Off > 35 UPDATE STATIST aropdat_i CR Wait 5 0 0 - Off > 29 sysadmin DR Wait 5 0 0 - Off > 28 sysadmin DR Wait 5 0 0 - Off > 27 UPDATE sysadmin DR Wait 5 0 0 - Off > > ------------------------------------------------------- > onstat -g act > ------------------------------------------------------- > IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:04:04 > --> 80384 Kbytes > > Running threads: > tid tcb rstcb prty status vp-class name > 8 83615030 0 1 running 8soc* soctcppoll > 9 836490e0 0 1 running 9soc* soctcpio > > ------------------------------------------------------- > onstat -l > ------------------------------------------------------- > IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:03:37 > --> 80384 Kbytes > > Physical Logging > Buffer bufused bufsize numpages numwrits pages/io > P-2 7 32 616 210 2.93 > > phybegin physize phypos phyused %used > > 2:53 12288 7547 10 0.08 > > Logical Logging > Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io > L-3 0 16 28049 5000 4643 5.6 1.1 > > Subsystem numrecs Log Space used > > OLDRSAM 27813 4025992 > > HA 236 10384 > > Buffer Waiting > Buffer ioproc flags > L-3 0 0x21 0 > > address number flags uniqid begin size used %used > 00000000831CADB0 1 U------ 13 1:12763 2500 2500 100.00 > 00000000831CAE20 2 U---C-L 14 1:15263 2500 2499 99.96 > 00000000831CAE90 3 U------ 3 1:17763 2500 2266 90.64 > 00000000831CAF00 4 U------ 4 1:20263 2500 3 0.12 > 00000000831CAF70 5 U------ 5 1:22763 2500 3 0.12 > 00000000832D7460 6 U------ 6 1:25263 2500 3 0.12 > 00000000832D74D0 7 U------ 7 3:53 2560 3 0.12 > 00000000832D7540 8 U------ 8 3:2613 2560 3 0.12 > 00000000832D75B0 9 U------ 9 3:5173 2560 2560 100.00 > 00000000832D7620 10 U------ 10 3:7733 2560 2560 100.00 > 00000000832D7690 11 U------ 11 3:10293 2560 2560 100.00 > 00000000832D7700 12 U------ 12 3:12853 2560 2560 100.00 > 12 active, 12 total > > --------------------------------------------------------- > onstat -m > --------------------------------------------------------- > IBM Informix Dynamic Server Version 12.10.FC1DE -- On-Line -- Up 22:03:42 > --> 80384 Kbytes > > Message Log File: C:\\\\PROGRA~1\\\\IBMINF~1\\\\ol_informix1210_1.log > 18:18:04 Checkpoint Statistics - Avg. Txn Block Time 0.010, # Txns blocked > 1, > Plog used 3, Llog used 9 > > 18:18:05 Checkpoint Completed: duration was 0 seconds. > 18:18:05 Tue Oct 01 - loguniq 14, logpos 0x9bc018, timestamp: 0x7e3c5 > Interval: 699 > > 18:18:05 Maximum server connections 0 > 18:18:05 Checkpoint Statistics - Avg. Txn Block Time 0.010, # Txns blocked > 1, > Plog used 3, Llog used 10 > > 18:18:06 Logical Log Files are Full -- Backup is Needed > > 18:18:06 Checkpoint Completed: duration was 0 seconds. > 18:18:06 Tue Oct 01 - loguniq 14, logpos 0x9c2018, timestamp: 0x7e3e2 > Interval: 700 > > 18:18:06 Maximum server connections 0 > 18:18:06 Checkpoint Statistics - Avg. Txn Block Time 0.010, # Txns blocked > 1, > Plog used 3, Llog used 6 > > 18:37:26 listener-thread: err = -951: oserr = 0: errstr = > informix@ARSIQA-64: > Incorrect password or user informix@ARSIQA-64 is not known on the database > server. > > 19:20:15 listener-thread: err = -951: oserr = 0: errstr = > informix@ARSIQA-64: > Incorrect password or user informix@ARSIQA-64 is not known on the database > server. > > That event on 18:18:06 i handled by ontape -a. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1135e5a401ba2f04e7b404f3

Thank you very much. It was really problem with logs. I run ontape -a and everything works now. But it is curious because i run ontape -a a few hours before when the IDS did not want to start at all.