All Forums General
vishaman 16 posts Joined 05/13
18 Jul 2014
Replace a String in column

Hi 
I have a a huge task to re-write all the views on the databases  to "Locking row for access" ,I have been working all this week to get a script for this puproose ,I have been able to dynamically eliminate all the invalid views and dump the data into a flat file ..so my file looks like this now ...
Databasename | Tablename | Requesttext (i.e., the definition of view )
I have loaded this data intoa temp table again and am trying to replace the string "AS ( SELECT " with " AS LOCKING ROW FOR ACCESS ( SELECT "
SEL oreplace  (REQUESTTEXT, 'AS ( SELECT' , 'AS LOCKING ROW FOR ACCESS (SELECT ') FROM Mydatabase.temp_table;
but somehow  i get this instead 
SELECT Failed. 7504:  in UDF/XSP/UDM SYSLIB.oreplace2: SQLSTATE ."Par: 
Is there a better way of doing this ? I dont want to manually update the views ,there are thousands of them !!!
 
PLease help 
 
 

Raja_KT 1246 posts Joined 07/09
18 Jul 2014

Which TD Version you are using? Do you have oreplace2? You can check with your DBA if you do not have.
You can use regexp_replace thus:
select regexp_replace('replace view as (select','as \(select','as locking row for access select (', 1,1,'c')
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Ashoktera 12 posts Joined 03/14
19 Jul 2014

 
From which version the string functions like 'replace' and 'translate' added. I not able to see them working in  TD14 even. Is there a seperate licence for it.
 
--Ashok
 

Ashok

Raja_KT 1246 posts Joined 07/09
19 Jul 2014

It does not require license. You have in vmware too. If I am not wrong,in  TD 14 it's introduced. I work on 14.10 and it is very much there.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
20 Jul 2014

Does your site follow the recommendation to create 1:1 views for every base table?
Then there should be a process already to create those and you might simply add the access lock to it.
When there are additional views on top of those base views you don't need to add an access lock again.
 
Regarding your approach, what's your TD release? In TD14 your OREPLACE should work as-is. 

Dieter

vishaman 16 posts Joined 05/13
20 Jul 2014

Hi 
I am on 13.10.05.04, While I had no issue doing this .
 sel oreplace(requesttext,';;',';') from Mydatabase.temp_table; 
but somehow this does not work....
SEL oreplace  (REQUESTTEXT, 'AS ( SELECT' , 'AS LOCKING ROW FOR ACCESS (SELECT ') FROM Mydatabase.temp_table;
SELECT Failed. 7504:  in UDF/XSP/UDM SYSLIB.oreplace2: SQLSTATE ."Par: 
No , they dont follow 1:1 

dnoeth 4628 posts Joined 11/04
20 Jul 2014

Check the definition of your OReplace UDF, it might not work with Unicode columns or the maximum size for the input or output column is smaller then the size of the requesttext. 
There will be workarounds like using POSITION and SUBSTRING instead of OReplace, but as you already exported the data to a flat file, it might be easier to do the processing directly on Unix using awk or Perl or whatever tool you know (and then simply submit the resulting file using BTEQ)...
 

Dieter

vishaman 16 posts Joined 05/13
21 Jul 2014

Thanks Dieter 
That's exactly what i did ,I replaced these strings using "sed" filter and now running an explain to see if it comes out clean however Now  there's is just one type of views that i cant imagine how to modify ...those are like 
replace view view1 (col1,col2...) AS LOCK TABLE <databasename.tablename> FOR ACCESS....
Here <databasename.tablename> is a variable string so i cant just do the usual find and replace ...
By the way my export file looks like this
Databasename|tablename|<requesttext>
Not sure if i should be worrying about these type of views ,will they cause session blockings ? ....should i leave them the way they are ?

06 Aug 2014

Hi Vishaman,
i checked on your requirement , i got the desired O/P as per your need.

 
please correct me if i am worng .
 
--Thanks
Razzendra
 

06 Aug 2014

Hi vishman,
 
here i tried with real code which has more number of columns in requesttext , still i could able to see expected result.
+++++++++++++++++++++++++++++++++++++++++++
SEL oreplace  ('REPLACE VIEW sales.retail AS ( SELECT
  column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
, column
from sales_app.retail;', 'AS ( SELECT' , 'AS LOCKING ROW FOR ACCESS (SELECT ')

;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

oreplace('REPLACE VIEW sales.retail AS ( SELECT   column , c
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
REPLACE VIEW sales.retail AS LOCKING ROW FOR ACCESS (SELECT    column , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  , column  from sales_app.retail;
 
+++++++++++++++++++++++++++++++++++++++++++
 
---- Rajendra Reddy
 

You must sign in to leave a comment.