HPL unload on secondary HDR

We have a backup script that does HPL unloads on certain tables throughout the day. When I run the scripts on an HDR secondary however, it hangs for a bit, then times out with error "Cannot open database 'sysmaster@servershm' - SQL error -25588, ISAM error 2". The strange part is that the server name it reports is the shared memory instance on the HDR primary. Is there a way to force HPL to use the local HDR secondary instance instead of the HDR primary instance? -Justin

Original post: We have a backup script that does HPL unloads on certain tables throughout the day. When I run the scripts on an HDR secondary however, it hangs for a bit, then times out with error "Cannot open database 'sysmaster@servershm' - SQL error -25588, ISAM error 2". The strange part is that the server name it reports is the shared memory instance on the HDR primary. Is there a way to force HPL to use the local HDR secondary instance instead of the HDR primary instance? -Justin Response: Ok, I didn't have a chance to try this, but it might work. What I'd suggest you do is start with 1 of your jobs and create a duplicate of the job with basically a different name...something with sec in it so you know it's the job for the secondary. Then on your primary, as some user who has write access to the onpload database, connect to that database with dbaccess. Then find the row in the session table for that new duplicate job. When you select that row you should notice there is a column called "server". I'm guessing that in your case, this column is set to "servershm" or whatever you have it set to for your primary instance shared memory connection. I think what you could then try is to update that column for just that row and set the server value to the shared memory INFORMIXSERVER value for your secondary. After you have it updated, make sure you then see the change on the secondary and then try to run that version of the job on the secondary. Since I didn't get a chance to test this, I'm not certain that's where the server name for the sysmaster@server command that the onpload job is coming from, but it was the only places I found any sort of server in the onpload tables in my quick search. Things to be careful of, the session table doesn't have any index (unique or otherwise) on it, so when you do your update statement, obviously make sure you are only updating that 1 row (don't want you changing multiple rows in your onpload database and messing up jobs that do work on your primary). If the above doesn't work, I'll see about setting up a hdr pair on different machines (usually most of my testing is done with the primary and secondary on the same machine so in that case the shared memory connection on the primary would probably still work for me) and see what else I might have missed. Also something to note, if you do get the hpl unload job running on your secondary, I assume you are ok with the fact that the table could be getting modified on the primary while the unload is happening on the secondary and you'd possibly pick up some of those changes while the unload was happening? Jacques Renaut IBM Informix Advanced Support APD Team

So, if I understand this correctly, when an HPL job is created, it is tied to the server that created it? If this is the case, then if I modify the job creation script to use a name that makes it unique (e.g. append the server name), then it should work okay, assuming that the server that I create the job on is the same as the one I run it on? -Justin -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JACQUES RENAUT Sent: Thursday, July 25, 2013 1:08 PM To: ids@iiug.org Subject: Re: HPL unload on secondary HDR [30952] Original post: We have a backup script that does HPL unloads on certain tables throughout the day. When I run the scripts on an HDR secondary however, it hangs for a bit, then times out with error "Cannot open database 'sysmaster@servershm' - SQL error -25588, ISAM error 2". The strange part is that the server name it reports is the shared memory instance on the HDR primary. Is there a way to force HPL to use the local HDR secondary instance instead of the HDR primary instance? -Justin Response: Ok, I didn't have a chance to try this, but it might work. What I'd suggest you do is start with 1 of your jobs and create a duplicate of the job with basically a different name...something with sec in it so you know it's the job for the secondary. Then on your primary, as some user who has write access to the onpload database, connect to that database with dbaccess. Then find the row in the session table for that new duplicate job. When you select that row you should notice there is a column called "server". I'm guessing that in your case, this column is set to "servershm" or whatever you have it set to for your primary instance shared memory connection. I think what you could then try is to update that column for just that row and set the server value to the shared memory INFORMIXSERVER value for your secondary. After you have it updated, make sure you then see the change on the secondary and then try to run that version of the job on the secondary. Since I didn't get a chance to test this, I'm not certain that's where the server name for the sysmaster@server command that the onpload job is coming from, but it was the only places I found any sort of server in the onpload tables in my quick search. Things to be careful of, the session table doesn't have any index (unique or otherwise) on it, so when you do your update statement, obviously make sure you are only updating that 1 row (don't want you changing multiple rows in your onpload database and messing up jobs that do work on your primary). If the above doesn't work, I'll see about setting up a hdr pair on different machines (usually most of my testing is done with the primary and secondary on the same machine so in that case the shared memory connection on the primary would probably still work for me) and see what else I might have missed. Also something to note, if you do get the hpl unload job running on your secondary, I assume you are ok with the fact that the table could be getting modified on the primary while the unload is happening on the secondary and you'd possibly pick up some of those changes while the unload was happening? Jacques Renaut IBM Informix Advanced Support APD Team ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Original post: So, if I understand this correctly, when an HPL job is created, it is tied to the server that created it? If this is the case, then if I modify the job creation script to use a name that makes it unique (e.g. append the server name), then it should work okay, assuming that the server that I create the job on is the same as the one I run it on? -Justin Response: Hmm, what version are you running? I just tried to run a onpload job on my secondary and got the following message: onpload -p jr -j cust_unl -fuThe onpload utility is not supported on a secondary server in a cluster. So I'm not sure if you have a version earlier then me that didn't have that restriction in yet, or a newer version where we've possibly relaxed the restriction. Jacques Renaut IBM Informix Advanced Support APD Team