title: Teradata SQL Performance Tuning: STRTOK_SPLIT_TO_TABLE: EXPLAIN Failed. 3738: String is longer than 31000 characters
body: <p>I am again probably hoping against hope but while using STRTOK_SPLIT_TO_TABLE on an in-list, where, my in-list is > 32K Character, it gives me the above error</p>
  
  <pre><code>EXPLAIN Failed. 3738:  String is longer than 31000 characters
  </code></pre>
  
  <h2>What I did</h2>
  
  <p>Split the string and used 2 STRTOK_SPLIT_TO_TABLE creating 2 different tables
  and then joined to my main table</p>
  
  <p>The actual Error translation from TD Error documentation advises to use "USING followed by data parcel". As I understand you can't do all that stuff in UDF
  So other than split the string, is there any other approach?
  I have 400K in-list strings that I want to avoid loading to VT because that seems to have its own overhead cost, so using this function, but it never worked.</p>
  
  <hr>
  
  <pre><code>    EXPLAIN 
  SELECT  COL1 
  FROM    DB.TB1 , ( 
  SELECT  TOKEN2 
  FROM    TABLE ( STRTOK_SPLIT_TO_TABLE( 1 , '123456,123456,43456,......20K Long string' ,
  ','  ) 
  RETURNS ( OUTKEY INT , TOKENNUM INT , TOKEN2 VARCHAR ( 20 ) CHARACTER 
  SET unicode ) ) DD ) D2 ,
  
  
   ( 
  SELECT  TOKEN1
  FROM    TABLE ( STRTOK_SPLIT_TO_TABLE( 1 , '123456,123456,43456.....20K long string' ,','  ) 
  RETURNS ( OUTKEY INT , TOKENNUM INT , TOKEN1 VARCHAR ( 20 ) CHARACTER 
  SET unicode ) ) DD ) D1 
  
  WHERE   SUBSTR ( TB1.COL2 , 21 , 9 ) = d2.token2 
  
  or  SUBSTR ( TB1.COL2 , 21 , 9 ) = d1.token1
  GROUP BY 1 ;
  
  
  /* the same logic can be re-wrritten 
     with a UNION  or  Where EXISTS logic  
        but focus's on that UDF */
  </code></pre>
  
  <p>So to summarize ....</p>
  
  <ul>
  <li><p>I have to FURTHER SPLIT the String length to smaller bits. Two is not enough. How do you harness the UDF features when the string is really that big that its worth running the Split string UDF?
  This is the error I get when I use smaller strings. My <code>explain</code> will run fine, because the UDF's throwing this err</p>
  
  <pre><code>Query Failed. 9134:  STRTOK: Input string exceeded the max. allowed string   length. 
  </code></pre></li>
  <li><p>It will NOT let me use LATIN. My Tables are LATIN so it uses translate overhead that causes char set conversion. Is there a work-around here? (Maybe translate the output column from the UDF yourself so the optimizer doesn't do it on the the join column. But that wouldn't matter so much in terms of improving since the optimizer would choose to translate the char set with lesser rows anyway )</p></li>
  </ul>
  
  <p>BTW: I am on 14.1 and as per below</p>
  
  <pre><code>    instring
  a character or CLOB argument.
  If instring is NULL, the STRTOK_SPLIT_TO_TABLE returns NULL.
  </code></pre>
  
  <p>From the documentation - I can stick a CLOB for instring. So even AFTER my > 32K string is Cast as CLOB it throws</p>
  
  <pre><code>EXPLAIN Failed. 3738:  String is longer than 31000 characters
  </code></pre>
  
  <p>Finally ....Using character_length ('string in D1') and (' ditto d2') I get 19K. It says varchar (32000 ) limit for instring, so wondered why the reject?</p>
  
  <p><strong>Update</strong>  : I could CLOB D1 and D2 and limit to a 2 strings approach and sneak out of the 9134 error . But 2 Caveats  still exist -  </p>
  
  <ul>
  <li>this UDF only likes UNICODE for token . I added a 'translation' to the token for whatever it's worth. </li>
  <li><p>I dont quite follow how the 32K  Logic works here when I test it with this </p>
  
  <p><code>sel Character_length ('string here' )</code></p>
  
  <p>Returns me a number  &lt; 32K so my string should not be subject to the 32K cap</p></li>
  </ul>
  
  <p>So using this in the smaller component of the query works fine. I can convert the string list to tables , have these join and pull out the ID list that I was interested but when I plug in the component into the bigger report , its freaks out - "segementation fault in Local AMP dont re-submit" ....
  So I had to adapt another approch. </p>