Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is the Stored Procedure code written in DB2 V9.7 LUW FP5

**CREATE OR REPLACE PROCEDURE USP_TEST
(
IN LOC_LIST XML)


P1: BEGIN ATOMIC

DECLARE IV_SOV_LOC_ID                 BIGINT;
DECLARE IV_SEC_MOD XML;

DECLARE SQLCODE INTEGER DEFAULT 0; 
DECLARE retcode INTEGER DEFAULT 1; 
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE INPUT_CURSOR CURSOR FOR
SELECT T.SOVLOCID,T.SEC_MOD
FROM 
XMLTABLE('$d/SOVLocations/SOVLocation' PASSING LOC_LIST AS "d"
COLUMNS
SOVLOCID BIGINT PATH 'SOVLocID',
SEC_MOD XML PATH 'SecondaryModifiers') AS T ;

DECLARE GLOBAL TEMPORARY TABLE 
          SESSION.GTT_SEC_MOD
        (
            SOV_LOC_ID              BIGINT,
            CODE_GROUP_VALUE    VARCHAR(100),           
            CODE_VALUE          VARCHAR(100) 
        )
                   WITH REPLACE in UTEMP4K
                   on commit preserve rows not logged;

OPEN INPUT_CURSOR;



FETCH INPUT_CURSOR INTO  IV_SOV_LOC_ID,IV_SEC_MOD ;

SET retcode = SQLCODE;

WHILE (retcode = 0) DO

INSERT INTO SESSION.GTT_SEC_MOD
                  ( 
                  SOV_LOC_ID,
                  CODE_VALUE,
                  CODE_GROUP_VALUE
                  )          
SELECT   
                     IV_SOV_LOC_ID,
                     X.CODE_VALUE ,
                     X.CODE_GROUP_VALUE 
             FROM  XMLTABLE('$d/SecondaryModifiers/SecondaryModifier' PASSING (IV_SEC_MOD) AS "d"
                  COLUMNS
                           CODE_VALUE       VARCHAR(100)       PATH 'ModifierValue',
                           CODE_GROUP_VALUE VARCHAR(100)  PATH 'ModifierName' 
                           ) AS X;


FETCH INPUT_CURSOR INTO  IV_SOV_LOC_ID,IV_SEC_MOD ;

SET retcode = SQLCODE;

END WHILE;

END P1 **

When I provide the following data as its parameter, it runs fine

<SOVLocations>
    <SOVLocation>
        <SOVLocID>6465285</SOVLocID>
        <StrBasicDataIncomplete>Y</StrBasicDataIncomplete>
        <LocationName/>
        <GGABasicDataIncomplete>Y</GGABasicDataIncomplete>
        <Address>220 Lake Dr</Address>
        <District/>
        <City>Newark</City>
        <ZipCode>19702-3353</ZipCode>
        <County>New Castle</County>
        <State>DE</State>
        <CntryCodeSch>ISO3A</CntryCodeSch>
        <CountryCD>USA</CountryCD>
        <LocationNo>10005</LocationNo>
        <CompanyName/>
        <SourceFileName>sov_99</SourceFileName>
        <SiteName/>
        <PrimaryBuilding/>
        <Latitude>39.614578</Latitude>
        <Longitude>-75.755150</Longitude>
        <NoOfRisks>2</NoOfRisks>
        <CurrencyCD>001</CurrencyCD>
        <BuildingValue>0.00</BuildingValue>
        <ContentValue>0.00</ContentValue>
        <TimeElmtValue>0000.00</TimeElmtValue>
        <TPDValue>0.00</TPDValue>
        <TSIValue>0000.0000</TSIValue>
        <NoOfStories>2</NoOfStories>
        <YearBuilt>1940</YearBuilt>
        <YearUpgrade>1950</YearUpgrade>
        <ConstTypCD>2</ConstTypCD>
        <ConstClsfctn_Scheme>ATC</ConstClsfctn_Scheme>
        <OccupancyTypCD>4</OccupancyTypCD>
        <OccupancyClsfctn_Scheme>ATC</OccupancyClsfctn_Scheme>
        <FloorArea>2000.0000</FloorArea>
        <FloorAreaUnit>Square Feet</FloorAreaUnit>
        <FloorOfInterest/>
        <BuildingHeight/>
        <BuildingHeightUnit/>
        <ConstructionDesc/>
        <OccupancyDesc/>
        <ActiveIn>Y</ActiveIn>
        <AutoAugmentIn>Y</AutoAugmentIn>
        <ProcessingStatusCode>PENDING_DA</ProcessingStatusCode>
        <Notes/>
        <LocationNote/>
        <IsSecModModified>Y</IsSecModModified>
        <SecondaryModifiers>
            <SecondaryModifier>
                <ModifierName>CONTENT MODIFIER</ModifierName>
                <ModifierValue>1</ModifierValue>
            </SecondaryModifier>
            <SecondaryModifier>
                <ModifierName>ORNAMENT</ModifierName>
                <ModifierValue>2</ModifierValue>
            </SecondaryModifier>
        </SecondaryModifiers>
    </SOVLocation>
    <SOVLocation>
        <SOVLocID>6465286</SOVLocID>
        <StrBasicDataIncomplete>Y</StrBasicDataIncomplete>
        <LocationName/>
        <GGABasicDataIncomplete>Y</GGABasicDataIncomplete>
        <Address/>
        <District/>
        <City>Hollywood</City>
        <ZipCode>1233312</ZipCode>
        <County/>
        <State>FL</State>
        <CntryCodeSch>ISO3A</CntryCodeSch>
        <CountryCD>USA</CountryCD>
        <LocationNo>10007</LocationNo>
        <CompanyName/>
        <SourceFileName>sov_99</SourceFileName>
        <SiteName/>
        <PrimaryBuilding/>
        <Latitude/>
        <Longitude/>
        <NoOfRisks>2</NoOfRisks>
        <CurrencyCD>001</CurrencyCD>
        <BuildingValue>0.00</BuildingValue>
        <ContentValue>0.00</ContentValue>
        <TimeElmtValue>0000.00</TimeElmtValue>
        <TPDValue>0.00</TPDValue>
        <TSIValue>0000.0000</TSIValue>
        <NoOfStories>2</NoOfStories>
        <YearBuilt>1940</YearBuilt>
        <YearUpgrade>1950</YearUpgrade>
        <ConstTypCD>2</ConstTypCD>
        <ConstClsfctn_Scheme>ATC</ConstClsfctn_Scheme>
        <OccupancyTypCD>17</OccupancyTypCD>
        <OccupancyClsfctn_Scheme>ATC</OccupancyClsfctn_Scheme>
        <FloorArea>2000.0000</FloorArea>
        <FloorAreaUnit>Square Feet</FloorAreaUnit>
        <FloorOfInterest/>
        <BuildingHeight/>
        <BuildingHeightUnit/>
        <ConstructionDesc/>
        <OccupancyDesc/>
        <ActiveIn>Y</ActiveIn>
        <AutoAugmentIn>N</AutoAugmentIn>
        <ProcessingStatusCode>PENDING_DA</ProcessingStatusCode>
        <Notes/>
        <LocationNote/>
        <IsSecModModified>Y</IsSecModModified>
        <SecondaryModifiers>
            <SecondaryModifier>
                <ModifierName>CONTENT MODIFIER</ModifierName>
                <ModifierValue>2</ModifierValue>
            </SecondaryModifier>
            <SecondaryModifier>
                <ModifierName>PLUMBING</ModifierName>
                <ModifierValue>1</ModifierValue>
            </SecondaryModifier>
        </SecondaryModifiers>
    </SOVLocation>
</SOVLocations>

But when I provide the following, it crashes

**<SOVLocations>
    <SOVLocation>
        <SOVLocID>4814062</SOVLocID>
        <StrBasicDataIncomplete>Y</StrBasicDataIncomplete>
        <LocationName>GPI  not sold - marketing</LocationName>
        <GGABasicDataIncomplete>Y</GGABasicDataIncomplete>
        <Address>8975 Shaver Road</Address>
        <District/>
        <City>Kalamazoo</City>
        <ZipCode>49002</ZipCode>
        <County/>
        <State>MI</State>
        <CntryCodeSch>ISO3A</CntryCodeSch>
        <CountryCD>USA</CountryCD>
        <LocationNo>45</LocationNo>
        <CompanyName/>
        <SourceFileName>UNC MHCELAC</SourceFileName>
        <SiteName/>
        <PrimaryBuilding/>
        <Latitude>42.187329</Latitude>
        <Longitude>-85.601868</Longitude>
        <NoOfRisks>1</NoOfRisks>
        <CurrencyCD>001</CurrencyCD>
        <BuildingValue>20.40</BuildingValue>
        <ContentValue>0.00</ContentValue>
        <TimeElmtValue>0.00</TimeElmtValue>
        <TPDValue>000000.4000</TPDValue>
        <TSIValue>000000.4000</TSIValue>
        <NoOfStories>0</NoOfStories>
        <YearBuilt/>
        <YearUpgrade/>
        <ConstTypCD>0</ConstTypCD>
        <ConstClsfctn_Scheme>RMS</ConstClsfctn_Scheme>
        <OccupancyTypCD>15</OccupancyTypCD>
        <OccupancyClsfctn_Scheme>ATC</OccupancyClsfctn_Scheme>
        <FloorArea>0.0000</FloorArea>
        <FloorAreaUnit>Square Feet</FloorAreaUnit>
        <FloorOfInterest/>
        <BuildingHeight/>
        <BuildingHeightUnit/>
        <ConstructionDesc/>
        <OccupancyDesc/>
        <ActiveIn>Y</ActiveIn>
        <AutoAugmentIn>N</AutoAugmentIn>
        <ProcessingStatusCode>PENDING_DA</ProcessingStatusCode>
        <Notes/>
        <LocationNote/>
        <IsSecModModified>Y</IsSecModModified>
        <SecondaryModifiers>
            <SecondaryModifier>
                <ModifierName>CONTENT MODIFIER</ModifierName>
                <ModifierValue>1</ModifierValue>
            </SecondaryModifier>
            <SecondaryModifier>
                <ModifierName>ORNAMENT</ModifierName>
                <ModifierValue>2</ModifierValue>
            </SecondaryModifier>
        </SecondaryModifiers>
    </SOVLocation>
    <SOVLocation>
        <SOVLocID>4814063</SOVLocID>
        <StrBasicDataIncomplete>Y</StrBasicDataIncomplete>
        <LocationName>Coates Screen Inks GmbH</LocationName>
        <GGABasicDataIncomplete>Y</GGABasicDataIncomplete>
        <Address>Mainstr. 9,Maiach</Address>
        <District/>
        <City>N├╝rnberg</City>
        <ZipCode>90451</ZipCode>
        <County/>
        <State>Bayern</State>
        <CntryCodeSch>ISO3A</CntryCodeSch>
        <CountryCD>DEU</CountryCD>
        <LocationNo>135</LocationNo>
        <CompanyName/>
        <SourceFileName>SUN CHEMICAL</SourceFileName>
        <SiteName/>
        <PrimaryBuilding/>
        <Latitude>49.413774</Latitude>
        <Longitude>11.053961</Longitude>
        <NoOfRisks>1</NoOfRisks>
        <CurrencyCD>001</CurrencyCD>
        <BuildingValue>1000.00</BuildingValue>
        <ContentValue>1000.50</ContentValue>
        <TimeElmtValue>00000.00</TimeElmtValue>
        <TPDValue>00000.0000</TPDValue>
        <TSIValue>00000.0000</TSIValue>
        <NoOfStories>3</NoOfStories>
        <YearBuilt>1995</YearBuilt>
        <YearUpgrade/>
        <ConstTypCD>3</ConstTypCD>
        <ConstClsfctn_Scheme>RMS IND</ConstClsfctn_Scheme>
        <OccupancyTypCD>2011</OccupancyTypCD>
        <OccupancyClsfctn_Scheme>RMS IND</OccupancyClsfctn_Scheme>
        <FloorArea>0.0000</FloorArea>
        <FloorAreaUnit>Square Feet</FloorAreaUnit>
        <FloorOfInterest/>
        <BuildingHeight/>
        <BuildingHeightUnit/>
        <ConstructionDesc/>
        <OccupancyDesc/>
        <ActiveIn>Y</ActiveIn>
        <AutoAugmentIn>Y</AutoAugmentIn>
        <ProcessingStatusCode>PENDING_DA</ProcessingStatusCode>
        <Notes/>
        <LocationNote/>
        <IsSecModModified>Y</IsSecModModified>
        <SecondaryModifiers>
            <SecondaryModifier>
                <ModifierName>CONTENT MODIFIER</ModifierName>
                <ModifierValue>2</ModifierValue>
            </SecondaryModifier>
            <SecondaryModifier>
                <ModifierName>STORYPROF</ModifierName>
                <ModifierValue>2</ModifierValue>
            </SecondaryModifier>
        </SecondaryModifiers>
    </SOVLocation>
</SOVLocations> **

We were able to figure out that CURSORS with XML Columns might be one of the culprits, but not sure why it works fine for certain data and not for the other.

share|improve this question
    
Define "it crashes". –  mustaccio Nov 19 '13 at 18:50
    
Instance gets trapped –  Senthil Nov 19 '13 at 18:57
    
In that case your best bet is to contact IBM support. –  mustaccio Nov 19 '13 at 19:14
    
I have already done it. But I wanted to know if the community can get me the answer faster. –  Senthil Nov 19 '13 at 19:27
    
Anything show up in your administative log or diagnostic logs? Get any FODC files or trap files? –  Chris Aldrich Nov 20 '13 at 14:03
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.