--- In firebird-support@yahoogroups.com, "dkeith2" <dkeith2@...> wrote:
>
> I have a procedure where I query the following table:
>
> **********************************************************************
>
> CP_MONO_MAIN_PATI (
> ID INTEGER NOT NULL,
> CPNUM INTEGER NOT NULL,
> DFORM BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> DESCR BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> CONTRA BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> "_ADMIN" BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> MISSED BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> INTER BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> MONITOR BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> SIDE BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> STORE BLOB SUB_TYPE 1 SEGMENT SIZE 80,
> DFORMLAST VARCHAR(24),
> DESCRLAST VARCHAR(24),
> CONTRALAST VARCHAR(24),
> ADMINLAST VARCHAR(24),
> MISSEDLAST VARCHAR(24),
> INTERLAST VARCHAR(24),
> SIDELAST VARCHAR(24),
> MONITORLAST VARCHAR(24),
> STORELAST VARCHAR(24)
> );
>
> **********************************************************************
>
> Procedure:
>
> CREATE PROCEDURE PARSE_HTML_TAGS
> RETURNS (
> ATAG VARCHAR(255))
> AS
> DECLARE VARIABLE COMBINED BLOB SUB_TYPE 1 SEGMENT SIZE 80;
> DECLARE VARIABLE CNT INTEGER;
> DECLARE VARIABLE POS INTEGER;
> DECLARE VARIABLE LOC1 INTEGER;
> DECLARE VARIABLE LOC2 INTEGER;
> DECLARE VARIABLE TAG VARCHAR(255);
> BEGIN
> EXECUTE STATEMENT 'DELETE FROM TEMP WHERE USER_NAME = ''DKEITH''';
>
> FOR SELECT DISHEET
> FROM CP_MONO_MAIN_PATI_
> --WHERE UPPER(CAST(DESCR||CONTRA||"_ADMIN"||MISSED||INTER||MONITOR||SIDE||STORE as VARCHAR(16384))) like '%<B%'
> INTO :COMBINED
> DO
> BEGIN
> POS = 1;
> LOC1 = 1;
> LOC2 = 3;
> WHILE ((:LOC2 > 2) AND (:LOC1 > 0)) DO
> BEGIN
> LOC1 = POSITION('<',:COMBINED,:POS);
> LOC2 = POSITION('>',:COMBINED,:POS);
> IF ((:LOC1 > 0) AND (:LOC2 > 0) AND ((:LOC2 - :LOC1 + 1) > 0)) THEN
> TAG = UPPER(SUBSTRING(:COMBINED FROM :LOC1 FOR ((:LOC2 - :LOC1) + 1)));
> POS = :LOC2 + 1;
> /*
> SELECT COUNT(*) FROM TEMP
> WHERE STRINGDATA = :TAG
> AND USER_NAME = 'DKEITH'
> INTO :CNT;
> IF (:CNT = 0) THEN
> BEGIN */
> INSERT INTO TEMP(USER_NAME,STRINGDATA) VALUES('DKEITH',:TAG);
> --IF (POSITION('/' IN :TAG) <> 2) THEN
> --SUSPEND;
> --END
> END
>
> POS = 1;
> LOC1 = 1;
> LOC2 = 3;
> WHILE ((:LOC2 > 2) AND (:LOC1 > 0)) DO
> BEGIN
> LOC1 = POSITION('&',:COMBINED,:POS);
> LOC2 = POSITION(';',:COMBINED,:POS);
> IF ((:LOC1 > 0) AND (:LOC2 > 0) AND ((:LOC2 - :LOC1 + 1) > 0)) THEN
> TAG = UPPER(SUBSTRING(:COMBINED FROM :LOC1 FOR ((:LOC2 - :LOC1) + 1)));
> POS = :LOC2 + 1;
> /*
> SELECT COUNT(*) FROM TEMP
> WHERE STRINGDATA = :TAG
> AND USER_NAME = 'DKEITH'
> INTO :CNT;
> IF (:CNT = 0) THEN
> BEGIN */
> INSERT INTO TEMP(USER_NAME,STRINGDATA) VALUES('DKEITH',:TAG);
> --SUSPEND;
> --END
> END
> COMBINED = '';
> END
>
> insert into temp (user_name,stringdata2)
> select distinct user_name,stringdata from temp a;
>
> execute statement 'delete from temp where stringdata2 is null';
> execute statement 'update temp set stringdata = stringdata2, stringdata2 = null where user_name = ''DKEITH''';
> for select stringdata from temp
> into atag
> do
> suspend;
> /*
> FOR SELECT DESCR||CONTRA||"_ADMIN"||MISSED||INTER||MONITOR||SIDE||STORE
> FROM CP_MONO_MAIN_PATI
> --WHERE UPPER(CAST(DESCR||CONTRA||"_ADMIN"||MISSED||INTER||MONITOR||SIDE||STORE as VARCHAR(16384))) like '%ߦ%'
> INTO :COMBINED
> DO
> BEGIN
> POS = 1;
> LOC1 = 1;
> LOC2 = 3;
> WHILE ((:LOC2 > 2) AND (:LOC1 > 0)) DO
> BEGIN
> LOC1 = POSITION('&',:COMBINED,:POS);
> LOC2 = POSITION(';',:COMBINED,:POS);
> IF ((:LOC1 > 0) AND (:LOC2 > 0) AND ((:LOC2 - :LOC1 + 1) > 0)) THEN
> TAG = UPPER(SUBSTRING(:COMBINED FROM :LOC1 FOR ((:LOC2 - :LOC1) + 1)));
> POS = :LOC2 + 1;
>
> SELECT COUNT(*) FROM TEMP
> WHERE STRINGDATA = :TAG
> AND USER_NAME = 'DKEITH'
> INTO :CNT;
> IF (:CNT = 0) THEN
> BEGIN
> INSERT INTO TEMP(USER_NAME,STRINGDATA) VALUES('DKEITH',:TAG);
> SUSPEND;
> END
> END
> COMBINED = '';
> END */
> END
>
> **********************************************************************
>
> I received the following error while the procedure is executing:
>
> Unsuccessful execution caused by an unavailable resource.
> unable to allocate memory from operating system.
> At procedure 'PARSE_HTML_TAGS' line: 25, col: 7.
>
> Is there a setting in firebird.conf that would allow me to allocate more memory to the instance so that this query could run? I'm the only user as this is a research database.
>
> **********************************************************************
>
> OS:
>
> Mandriva 2009
>
> **********************************************************************
>
> # cat /proc/meminfo:
>
> MemTotal: 3635128 kB
> MemFree: 264440 kB
> Buffers: 130204 kB
> Cached: 180000 kB
> SwapCached: 50576 kB
> Active: 3018292 kB
> Inactive: 251168 kB
> HighTotal: 2752256 kB
> HighFree: 4380 kB
> LowTotal: 882872 kB
> LowFree: 260060 kB
> SwapTotal: 4088500 kB
> SwapFree: 3836272 kB
> Dirty: 1392 kB
> Writeback: 0 kB
> AnonPages: 2948188 kB
> Mapped: 26500 kB
> Slab: 67200 kB
> SReclaimable: 56292 kB
> SUnreclaim: 10908 kB
> PageTables: 7608 kB
> NFS_Unstable: 0 kB
> Bounce: 0 kB
> WritebackTmp: 0 kB
> CommitLimit: 5906064 kB
> Committed_AS: 3798472 kB
> VmallocTotal: 114680 kB
> VmallocUsed: 21564 kB
> VmallocChunk: 92532 kB
> HugePages_Total: 0
> HugePages_Free: 0
> HugePages_Rsvd: 0
> HugePages_Surp: 0
> Hugepagesize: 4096 kB
> DirectMap4k: 4096 kB
> DirectMap4M: 913408 kB
>
> **********************************************************************
>
> # cat /proc/cpuinfo:
>
> processor : 0
> vendor_id : GenuineIntel
> cpu family : 15
> model : 4
> model name : Intel(R) Xeon(TM) CPU 3.20GHz
> stepping : 3
> cpu MHz : 3200.000
> cache size : 2048 KB
> physical id : 0
> siblings : 2
> core id : 0
> cpu cores : 1
> apicid : 0
> initial apicid : 0
> fdiv_bug : no
> hlt_bug : no
> f00f_bug : no
> coma_bug : no
> fpu : yes
> fpu_exception : yes
> cpuid level : 5
> wp : yes
> flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pebs bts pni monitor ds_cpl cid cx16 xtpr
> bogomips : 6384.30
> clflush size : 64
> power management:
>
> processor : 1
> vendor_id : GenuineIntel
> cpu family : 15
> model : 4
> model name : Intel(R) Xeon(TM) CPU 3.20GHz
> stepping : 3
> cpu MHz : 3200.000
> cache size : 2048 KB
> physical id : 0
> siblings : 2
> core id : 0
> cpu cores : 1
> apicid : 1
> initial apicid : 1
> fdiv_bug : no
> hlt_bug : no
> f00f_bug : no
> coma_bug : no
> fpu : yes
> fpu_exception : yes
> cpuid level : 5
> wp : yes
> flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pebs bts pni monitor ds_cpl cid cx16 xtpr
> bogomips : 6384.23
> clflush size : 64
> power management:
>
> *********************************************************************
>
> firebird.conf:
>
> #RootDirectory =
> #DatabaseAccess = Full
> ExternalFileAccess = Full
> #UdfAccess = Restrict UDF
> #TempDirectories =
> #LegacyHash = 1
> #Authentication = mixed
> #DefaultDbCachePages = 2048
> #DatabaseGrowthIncrement = 134217728
> #MaxFileSystemCache = 65536
> #TempBlockSize = 1048576
> #TempCacheLimit = 67108864
> TempCacheLimit = 671088604
> #OldParameterOrdering = 0
> #CompleteBooleanEvaluation = 0
> #DeadlockTimeout = 10
> #MaxUnflushedWrites = 100
> #MaxUnflushedWriteTime = 5
> #BugcheckAbort = 0
> #OldColumnNaming = 0
> #RelaxedAliasChecking = 0
> #ConnectionTimeout = 180
> #DummyPacketInterval = 0
> #RemoteServiceName = gds_db
> #RemoteServicePort = 3050
> #RemoteAuxPort = 0
> #TcpRemoteBufferSize = 8192
> #TcpNoNagle = 1
> #RemoteBindAddress =
> #LockMemSize = 1048576
> #LockSemCount = 32
> #LockGrantOrder = 1
> #LockAcquireSpins = 0
> #LockHashSlots = 1009
> #EventMemSize = 65536
> #CpuAffinityMask = 1
> #UsePriorityScheduler = 1
> #PrioritySwitchDelay = 100
> #PriorityBoost = 5
> #GCPolicy = combined
> #GuardianOption = 1
> #ProcessPriorityLevel = 0
> #IpcName = FIREBIRD
> #RemotePipeName = interbas
> #CreateInternalWindow = 1
> #LockSignal = 16
> #RemoteFileOpenAbility = 0
> #Redirection = 0
> **********************************************************************
>
> I've tried many permutations of this, but all eventually run out of resources.
>
> TIA.
>
> David Keith
>