Advanced B2B store add to cart not working
We installed the sample Advanced B2B store on a Commerce 5.6.1 server
with a remote DB2 database. When we try to add an item to the cart/order
we get an error on the web side (CMN3101E The system is unavailable due
to "CMN0417E".) and in the SystemOut log on the server we get the following:
[7/14/06 2:41:28:591 PDT] 18d01c4a CommerceSrvr A
com.ibm.commerce.order.commands.AllocateInventoryCmdImpl performExecute
CMN1018S: Checking inventory availability for the following OrderItems:
10003.
[7/14/06 2:41:28:599 PDT] 18d01c4a CommerceSrvr A
com.ibm.commerce.fulfillment.commands.StoredProcedureHelperProxy
callAvailInvStore CMN1011S: Invoking stored procedure "AvailInvStore".
[7/14/06 2:41:31:146 PDT] 18d01c4a CommerceSrvr E
com.ibm.commerce.fulfillment.commands.CheckInventoryAvailabilityCmdImpl
checkInventoryAvailabilty CMN0417E: The following SQL exception has
occurred during processing: "[IBM][CLI Driver][DB2/LINUXPPC] SQL0444N
Routine "*INVSTORE" (specific name "SQL060703135205510") is implemented
with code in library or path "...function/AVAILINVSTORE", function
"AVAILINVSTORE" which cannot be accessed. Reason code: "4". SQLSTATE=42724
".COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUXPPC]
SQL0444N Routine "*INVSTORE" (specific name "SQL060703135205510") is
implemented with code in library or path "...function/AVAILINVSTORE",
function "AVAILINVSTORE" which cannot be accessed. Reason code: "4".
SQLSTATE=42724
It looks like there are functions missing on the DB2 server but I can
not find any documentation for 5.6.1 that talks about copying these
functions to a remote DB2 server. Can someone tell me what needs to be
copied to the remote server to get this to work?
Re: Advanced B2B store add to cart not working
Your missing some required stored procedures on your db2 database. Try to solve it by running the createsp.db2.bat manually. Otherwise create an empty commerce db again using the createdb.bat command, or create the instance again.
I saw this failing on DB2 ESE installations where it seemed something todo with db user rights during the creation of a WebSphere Commerce instance
Good luck,
Taco
Re: Advanced B2B store add to cart not working
[email]thiddink@eniac.nl[/email] wrote:[color=blue]
> Your missing some required stored procedures on your db2 database. Try to solve it by running the createsp.db2.bat manually. Otherwise create an empty commerce db again using the createdb.bat command, or create the instance again.
> I saw this failing on DB2 ESE installations where it seemed something todo with db user rights during the creation of a WebSphere Commerce instance
>
> Good luck,
>
> Taco[/color]
I finally got around to running the createsp.db2.sh manually and it
still fails. The error message that we get is this (the actual error
message is a little different, but it maps to this SQL code):
SQL20135N
The specified SQL archive does not match the target environment. Reason
code = reason-code.
Explanation:
The specified SQL archive does not match the target environment for one
of the following reasons:
1 The operating system of the target environment is not the same as the
operating system on which the SQL archive was created.
2 The database type and level of the target environment is not the same
as the database type and level on which the SQL archive was created.
User Response:
Ensure that the environment on which the SQL archive was created matches
the target environment and reissue the command. If the environments do
not match, you must manually create the SQL routine using the target
environment.
sqlcode: -20135
sqlstate: 55046
The Commerce node is SuSe 9 on a 32-bit Intel architecture (VMWare) and
the DB2 node is Suse 9 on a 64-bit PPC architecture. Could this be a
problem?
Re: Advanced B2B store add to cart not working
I had this problems once on a database I had moved from one system to
another using DB2 backup/restore. What I had to do was to first drop the
SPs and then recreate them.
At that time, I dropped them manually, but I have since noticed a
dropsp.db2.bat script. Try to run that before createsp.db2.bat...
/Nicolai
KBDrand wrote:[color=blue]
> [email]thiddink@eniac.nl[/email] wrote:[color=green]
>> Your missing some required stored procedures on your db2 database. Try
>> to solve it by running the createsp.db2.bat manually. Otherwise create
>> an empty commerce db again using the createdb.bat command, or create
>> the instance again.
>> I saw this failing on DB2 ESE installations where it seemed something
>> todo with db user rights during the creation of a WebSphere Commerce
>> instance
>>
>> Good luck,
>>
>> Taco[/color]
>
> I finally got around to running the createsp.db2.sh manually and it
> still fails. The error message that we get is this (the actual error
> message is a little different, but it maps to this SQL code):
>
> SQL20135N
> The specified SQL archive does not match the target environment. Reason
> code = reason-code.
> Explanation:
> The specified SQL archive does not match the target environment for one
> of the following reasons:
> 1 The operating system of the target environment is not the same as the
> operating system on which the SQL archive was created.
> 2 The database type and level of the target environment is not the same
> as the database type and level on which the SQL archive was created.
> User Response:
> Ensure that the environment on which the SQL archive was created matches
> the target environment and reissue the command. If the environments do
> not match, you must manually create the SQL routine using the target
> environment.
> sqlcode: -20135
> sqlstate: 55046
>
>
> The Commerce node is SuSe 9 on a 32-bit Intel architecture (VMWare) and
> the DB2 node is Suse 9 on a 64-bit PPC architecture. Could this be a
> problem?[/color]
Re: Advanced B2B store add to cart not working
Nicolai Dufva Nielsen wrote:[color=blue]
> I had this problems once on a database I had moved from one system to
> another using DB2 backup/restore. What I had to do was to first drop the
> SPs and then recreate them.
>
> At that time, I dropped them manually, but I have since noticed a
> dropsp.db2.bat script. Try to run that before createsp.db2.bat...
>
> /Nicolai[/color]
I tried running that and I got this:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.ADJUSTINVENTORY" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.ALLOCATEITEM" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.ALLOCBORA" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.AVAILABLEINV" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.AVAILINVSTORE" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.AVAILRADATE" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.AVAILRECEIPTS" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.BACKORDERITEM" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.CURRENTVERSION" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.DELETEBACKORDER" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.GETITEMS" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.INVENTORYALLOCATION" is an undefined name.
SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.RAALLOCATION" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.REVERSEINVENTORY" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.SHIPITEMS" is an undefined name. SQLSTATE=42704
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.EXPECTEDINV" is an undefined name. SQLSTATE=42704
DB20000I The TERMINATE command completed successfully.
Looks like none of the Stored Procedures existed prior to me running the
create.
But when I try to run the createsp command I still get the following:
connect to demo user db2inst1 using
Database Connection Information
Database server = DB2/LINUXPPC 8.2.1
SQL authorization ID = DB2INST1
Local database alias = DEMO
set schema db2inst1
DB20000I The SQL command completed successfully.
put routine from availreceipts.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from currentversion.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from allocateitem.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from availableinv.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from allocbora.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from inventoryallocation.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from deletebackorder.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from getitems.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from raallocation.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from reverseinventory.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from shipitems.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from adjustinventory.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from backorderitem.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from availinvstore.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from availradate.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
put routine from expectedInv.spa owner db2inst1 use registers
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
connect reset
DB20000I The SQL command completed successfully.
DB20000I The TERMINATE command completed successfully.
The 20135 maps to the error that mentioned incompatible versions.
Re: Advanced B2B store add to cart not working
Well, I resolved the issue. The db2 server was Linux on PPC while the
Commerce server was Linux on Intel, so the stored procedures would not
install properly.
I had to go get the Linux PPC version of Commerce and install it on the
DB2 server, then run the createsp.db2.sh script locally.
Seems like such a waste of time/effort/space just to create the Stored
procedures. It would be nice if they were in a more portable format or
if there was an easier way to get them.
KBDrand wrote:[color=blue]
> Nicolai Dufva Nielsen wrote:[color=green]
>> I had this problems once on a database I had moved from one system to
>> another using DB2 backup/restore. What I had to do was to first drop
>> the SPs and then recreate them.
>>
>> At that time, I dropped them manually, but I have since noticed a
>> dropsp.db2.bat script. Try to run that before createsp.db2.bat...
>>
>> /Nicolai[/color]
>
> I tried running that and I got this:
>
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.ADJUSTINVENTORY" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.ALLOCATEITEM" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.ALLOCBORA" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.AVAILABLEINV" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.AVAILINVSTORE" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.AVAILRADATE" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.AVAILRECEIPTS" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.BACKORDERITEM" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.CURRENTVERSION" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.DELETEBACKORDER" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.GETITEMS" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.INVENTORYALLOCATION" is an undefined name.
> SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.RAALLOCATION" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.REVERSEINVENTORY" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.SHIPITEMS" is an undefined name. SQLSTATE=42704
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0204N "DB2INST1.EXPECTEDINV" is an undefined name. SQLSTATE=42704
> DB20000I The TERMINATE command completed successfully.
>
> Looks like none of the Stored Procedures existed prior to me running the
> create.
>
> But when I try to run the createsp command I still get the following:
>
> connect to demo user db2inst1 using
>
> Database Connection Information
>
> Database server = DB2/LINUXPPC 8.2.1
> SQL authorization ID = DB2INST1
> Local database alias = DEMO
>
>
> set schema db2inst1
> DB20000I The SQL command completed successfully.
>
> put routine from availreceipts.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from currentversion.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from allocateitem.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from availableinv.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from allocbora.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from inventoryallocation.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from deletebackorder.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from getitems.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from raallocation.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from reverseinventory.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from shipitems.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from adjustinventory.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from backorderitem.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from availinvstore.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from availradate.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> put routine from expectedInv.spa owner db2inst1 use registers
> SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR") has
> returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
> SQLSTATE=38000
>
> connect reset
> DB20000I The SQL command completed successfully.
>
> DB20000I The TERMINATE command completed successfully.
>
>
> The 20135 maps to the error that mentioned incompatible versions.
>
>[/color]
Re: Advanced B2B store add to cart not working
The db2 server was Linux on PPC while the Commerce server was Linux on Intel, so the stored procedures would not install properly.
