CLOB cloumn data not reflecting in HDR seconday
User on IDS 12.10.FC8 (Windows 2012 R2) with HDR found that a table's CLOB column returned NULL on the secondary, although the data was present on the primary; onstat -g dri showed replication apparently healthy. Respondents pointed out that smart large objects are only replicated if logging is enabled, and that logging can be set at sbspace, table or object level, suggesting checks with the knowledge-center docs, onstat -g smb s / oncheck -pS and dbschema -ss. The OP's output showed the sbspace defaults were LO_LOG (LO_LOG, LO_NOKEEP_LASTACCESS_TIME, LO_HIGH_INTEG), and the thread ends there with no resolution recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Im trying to retrieve data from one of the table consisting CLOB column, data
is not populating in HDR server it is showing NULL values. But CLOB data is
available in primary.
IDS version : 12.10 FC8
OS : windows server 2012 R2
HDR enabled.
output of onstat -g dri
Primary:
IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up 49 days
17:51:59 -- 7761024 Kbytes
Data Replication at 000000008BBAC030:
Type State Paired server Last DR CKPT (id/pg) Supports Proxy Writes
primary on hdr_secondary 641 / 84769 NA
DRINTERVAL 0
DRTIMEOUT 30
DRAUTO 3
DRLOSTFOUND D:\\\\Informix\\\\etc\\\\dr.lostfound
DRIDXAUTO 1
ENCRYPT_HDR 0Backlog 0
Last Send 2018/04/19 15:10:52
Last Receive 2018/04/19 15:10:52
Last Ping 2018/04/19 15:10:22
Last log page applied(log id,page): 641,97487
Secondary:
IBM Informix Dynamic Server Version 12.10.FC8 -- Read-Only (Sec) -- Up 49 days
17:50:23 -- 5566720 Kbytes
Data Replication at 000000008BBAC030:
Type State Paired server Last DR CKPT (id/pg) Supports Proxy Writes
HDR Secondary on primary 641 / 84769 N
DRINTERVAL 0
DRTIMEOUT 30
DRAUTO 3
DRLOSTFOUND D:\\\\Informix\\\\etc\\\\dr.lostfound
DRIDXAUTO 1
ENCRYPT_HDR 0Backlog 0
Last Send 2018/04/19 15:10:53
Last Receive 2018/04/19 15:10:53
Last Ping 2018/04/19 15:10:52
Last log page applied(log id,page): 0,0
Hi,
Is the CLOB in a logged sbspace and is logging enabled for that column?
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.admin.doc/
ids_admin_0707.htm
Regards,
David.
> On 19 April 2018 at 10:37 MUKESH TANUKU <mukeshbt1328@gmail.com> wrote:
>
>
> Im trying to retrieve data from one of the table consisting CLOB column, data
> is not populating in HDR server it is showing NULL values. But CLOB data is
> available in primary.
>
> IDS version : 12.10 FC8
> OS : windows server 2012 R2
> HDR enabled.
>
> output of onstat -g dri
>
> Primary:
>
> IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up 49 days
> 17:51:59 -- 7761024 Kbytes>
> Data Replication at 000000008BBAC030:
> Type State Paired server Last DR CKPT (id/pg) Supports Proxy Writes
> primary on hdr_secondary 641 / 84769 NA
>
> DRINTERVAL 0
> DRTIMEOUT 30
> DRAUTO 3
> DRLOSTFOUND D:\\\\Informix\\\\etc\\\\dr.lostfound
> DRIDXAUTO 1
> ENCRYPT_HDR 0> Backlog 0
> Last Send 2018/04/19 15:10:52
> Last Receive 2018/04/19 15:10:52
> Last Ping 2018/04/19 15:10:22
> Last log page applied(log id,page): 641,97487
>
> Secondary:
>
> IBM Informix Dynamic Server Version 12.10.FC8 -- Read-Only (Sec) -- Up 49days
> 17:50:23 -- 5566720 Kbytes
>
> Data Replication at 000000008BBAC030:
> Type State Paired server Last DR CKPT (id/pg) Supports Proxy Writes
> HDR Secondary on primary 641 / 84769 N
>
> DRINTERVAL 0
> DRTIMEOUT 30
> DRAUTO 3
> DRLOSTFOUND D:\\\\Informix\\\\etc\\\\dr.lostfound
> DRIDXAUTO 1
> ENCRYPT_HDR 0> Backlog 0
> Last Send 2018/04/19 15:10:53
> Last Receive 2018/04/19 15:10:53
> Last Ping 2018/04/19 15:10:52
> Last log page applied(log id,page): 0,0
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
CLOBS can be logged or unlogged. This can have defaults at the dbspace
level, table level and these can be overriden on a per object base.
I suppose none of the logging levels is set in your case.
Check:
onstat -g smb s
dbschema -d database -t sblob_table -ss
Regards.
On Thu, Apr 19, 2018 at 11:37 AM, MUKESH TANUKU <mukeshbt1328@gmail.com>
wrote:
> Im trying to retrieve data from one of the table consisting CLOB column,
> data
> is not populating in HDR server it is showing NULL values. But CLOB data
> is
> available in primary.
>
> IDS version : 12.10 FC8
> OS : windows server 2012 R2
> HDR enabled.
>
> output of onstat -g dri
>
> Primary:
>
> IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up 49> days
> 17:51:59 -- 7761024 Kbytes
>
> Data Replication at 000000008BBAC030:
> Type State Paired server Last DR CKPT (id/pg) Supports Proxy Writes
> primary on hdr_secondary 641 / 84769 NA
>
> DRINTERVAL 0
> DRTIMEOUT 30
> DRAUTO 3
> DRLOSTFOUND D:\\\\Informix\\\\etc\\\\dr.lostfound
> DRIDXAUTO 1
> ENCRYPT_HDR 0> Backlog 0
> Last Send 2018/04/19 15:10:52
> Last Receive 2018/04/19 15:10:52
> Last Ping 2018/04/19 15:10:22
> Last log page applied(log id,page): 641,97487
>
> Secondary:
>
> IBM Informix Dynamic Server Version 12.10.FC8 -- Read-Only (Sec) -- Up 49> days
> 17:50:23 -- 5566720 Kbytes
>
> Data Replication at 000000008BBAC030:
> Type State Paired server Last DR CKPT (id/pg) Supports Proxy Writes
> HDR Secondary on primary 641 / 84769 N
>
> DRINTERVAL 0
> DRTIMEOUT 30
> DRAUTO 3
> DRLOSTFOUND D:\\\\Informix\\\\etc\\\\dr.lostfound
> DRIDXAUTO 1
> ENCRYPT_HDR 0> Backlog 0
> Last Send 2018/04/19 15:10:53
> Last Receive 2018/04/19 15:10:53
> Last Ping 2018/04/19 15:10:52
> Last log page applied(log id,page): 0,0
>
>
>
*******************************************************************************
>
> 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...
Easier to use oncheck -pS instead of "onstat -g smb s"
Regards.
On Thu, Apr 19, 2018 at 12:55 PM, Fernando Nunes <domusonline@gmail.com>
wrote:
> CLOBS can be logged or unlogged. This can have defaults at the dbspace
> level, table level and these can be overriden on a per object base.
> I suppose none of the logging levels is set in your case.
>
> Check:
> onstat -g smb s
> dbschema -d database -t sblob_table -ss>
> Regards.
>
> On Thu, Apr 19, 2018 at 11:37 AM, MUKESH TANUKU <mukeshbt1328@gmail.com>
> wrote:
>
> > Im trying to retrieve data from one of the table consisting CLOB column,
> > data
> > is not populating in HDR server it is showing NULL values. But CLOB data
> > is
> > available in primary.
> >
> > IDS version : 12.10 FC8
> > OS : windows server 2012 R2
> > HDR enabled.
> >
> > output of onstat -g dri
> >
> > Primary:
> >
> > IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up 49> > days
> > 17:51:59 -- 7761024 Kbytes
> >
> > Data Replication at 000000008BBAC030:
> > Type State Paired server Last DR CKPT (id/pg) Supports Proxy Writes
> > primary on hdr_secondary 641 / 84769 NA
> >
> > DRINTERVAL 0
> > DRTIMEOUT 30
> > DRAUTO 3
> > DRLOSTFOUND D:\\\\Informix\\\\etc\\\\dr.lostfound
> > DRIDXAUTO 1
> > ENCRYPT_HDR 0> > Backlog 0
> > Last Send 2018/04/19 15:10:52
> > Last Receive 2018/04/19 15:10:52
> > Last Ping 2018/04/19 15:10:22
> > Last log page applied(log id,page): 641,97487
> >
> > Secondary:
> >
> > IBM Informix Dynamic Server Version 12.10.FC8 -- Read-Only (Sec) -- Up> 49
> > days
> > 17:50:23 -- 5566720 Kbytes
> >
> > Data Replication at 000000008BBAC030:
> > Type State Paired server Last DR CKPT (id/pg) Supports Proxy Writes
> > HDR Secondary on primary 641 / 84769 N
> >
> > DRINTERVAL 0
> > DRTIMEOUT 30
> > DRAUTO 3
> > DRLOSTFOUND D:\\\\Informix\\\\etc\\\\dr.lostfound
> > DRIDXAUTO 1
> > ENCRYPT_HDR 0> > Backlog 0
> > Last Send 2018/04/19 15:10:53
> > Last Receive 2018/04/19 15:10:53
> > Last Ping 2018/04/19 15:10:52
> > Last log page applied(log id,page): 0,0
> >
> >
> >
>
*******************************************************************************
>
> >
> > 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...
>
>
>
*******************************************************************************
>
> 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...
I have check this is it by default in logging for sbspace
find the output below:
IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up 49 days
20:22:48 -- 7761024 Kbytes
Sbspace Summary:
sbnum 11 address 8bec6970
Space : flags nchk owner sbname
-------- 8 informix sbspace1
Defaults : LO_LOG
LO : ud b/pg flags flags avg s/kb max lcks
4096 0 -------- -1 -1
Ext/IO : 1st sz/p nxt sz/p min sz/p mx io sz
0 0 0 -1
HdrCache : max free
512 1
sbnum 12 address 8bec6bc0
Space : flags nchk owner sbname
-------- 1 informix tmpsbspace
Defaults :
LO : ud b/pg flags flags avg s/kb max lcks
4096 0 -------- -1 -1
Ext/IO : 1st sz/p nxt sz/p min sz/p mx io sz
0 0 0 -1
HdrCache : max free
512 0
output of oncheck -pS sbapace1 is below:
D:\\\\Informix>oncheck -pS sbspace1 | findstr "Create Flags"
TBLspace Flags 2801 Page Locking
Created Sat Sep 16 14:43:37 2017
TBLspace Flags 200802 Row Locking
Created Sat Sep 16 14:43:37 2017
TBLspace Flags 200802 Row Locking
Created Sat Sep 16 14:43:37 2017
TBLspace Flags 200801 Page Locking
Created Sat Sep 16 14:43:37 2017
TBLspace Flags 200802 Row Locking
Created Sat Sep 16 14:43:37 2017
TBLspace Flags 200801 Page Locking
Created Sun Sep 17 02:41:54 2017
TBLspace Flags 200802 Row Locking
Created Sun Sep 17 02:41:54 2017
TBLspace Flags 200801 Page Locking
Created Sun Sep 17 02:43:08 2017
TBLspace Flags 200802 Row Locking
Created Sun Sep 17 02:43:08 2017
TBLspace Flags 200801 Page Locking
Created Sun Sep 17 02:45:27 2017
TBLspace Flags 200802 Row Locking
Created Sun Sep 17 02:45:27 2017
TBLspace Flags 200801 Page Locking
Created Sun Sep 17 02:50:12 2017
TBLspace Flags 200802 Row Locking
Created Sun Sep 17 02:50:12 2017
TBLspace Flags 200801 Page Locking
Created Sun Sep 17 02:59:43 2017
TBLspace Flags 200802 Row Locking
Created Sun Sep 17 02:59:43 2017
TBLspace Flags 200801 Page Locking
Created Sun Sep 17 03:18:05 2017
TBLspace Flags 200802 Row Locking
Created Sun Sep 17 03:18:05 2017
TBLspace Flags 200801 Page Locking
Created Tue Oct 24 19:53:50 2017
TBLspace Flags 200802 Row Locking
Created Tue Oct 24 19:53:50 2017
Weird output. -pS would show the entries for the smartblobs I think:
castelo@stardust.onlinedomus.net:informix-> oncheck -pS sbspace | grep
"Create Flags"
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME
LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME
LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME
LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME
LO_HIGH_INTEG
castelo@stardust.onlinedomus.net:informix->
And the table?
On Thu, Apr 19, 2018 at 2:27 PM, MUKESH TANUKU <mukeshbt1328@gmail.com>
wrote:
> output of oncheck -pS sbapace1 is below:
>
> D:\\\\Informix>oncheck -pS sbspace1 | findstr "Create Flags"
>
> TBLspace Flags 2801 Page Locking
>
> Created Sat Sep 16 14:43:37 2017
>
> TBLspace Flags 200802 Row Locking
>
> Created Sat Sep 16 14:43:37 2017
>
> TBLspace Flags 200802 Row Locking
>
> Created Sat Sep 16 14:43:37 2017
>
> TBLspace Flags 200801 Page Locking
>
> Created Sat Sep 16 14:43:37 2017
>
> TBLspace Flags 200802 Row Locking
>
> Created Sat Sep 16 14:43:37 2017
>
> TBLspace Flags 200801 Page Locking
>
> Created Sun Sep 17 02:41:54 2017
>
> TBLspace Flags 200802 Row Locking
>
> Created Sun Sep 17 02:41:54 2017
>
> TBLspace Flags 200801 Page Locking
>
> Created Sun Sep 17 02:43:08 2017
>
> TBLspace Flags 200802 Row Locking
>
> Created Sun Sep 17 02:43:08 2017
>
> TBLspace Flags 200801 Page Locking
>
> Created Sun Sep 17 02:45:27 2017
>
> TBLspace Flags 200802 Row Locking
>
> Created Sun Sep 17 02:45:27 2017
>
> TBLspace Flags 200801 Page Locking
>
> Created Sun Sep 17 02:50:12 2017
>
> TBLspace Flags 200802 Row Locking
>
> Created Sun Sep 17 02:50:12 2017
>
> TBLspace Flags 200801 Page Locking
>
> Created Sun Sep 17 02:59:43 2017
>
> TBLspace Flags 200802 Row Locking
>
> Created Sun Sep 17 02:59:43 2017
>
> TBLspace Flags 200801 Page Locking
>
> Created Sun Sep 17 03:18:05 2017
>
> TBLspace Flags 200802 Row Locking
>
> Created Sun Sep 17 03:18:05 2017
>
> TBLspace Flags 200801 Page Locking
>
> Created Tue Oct 24 19:53:50 2017
>
> TBLspace Flags 200802 Row Locking
>
> Created Tue Oct 24 19:53:50 2017
>
>
>
*******************************************************************************
>
> 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...
the output for oncheck -pS sbspace1 | FIND "Create Flags"
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Create Flags 0x31 LO_LOG LO_NOKEEP_LASTACCESS_TIME LO_HIGH_INTEG
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g