Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    7

    Unanswered: Need Solution for a Business Scenario (Sybase IQ)

    I am dealing with the following Business scenario:

    I have two tables T1 and T2.(I Production these table contain 100 Million Records or more)

    Table T1 contains following set of records:
    (Records for a ServiceID are chained inaccordance with their startdate and enddate i.e RECORD CHAINING)

    ServiceID IMEI startdate enddate
    100000001 1111 20081105 20081127
    100000001 2222 20081128 20081223
    100000001 3333 20081224 20090712
    100000001 4444 20090711 29990101
    200000002 5555 20081122 20081202
    200000002 6666 20081203 20090607
    200000002 7777 20090608 29990101
    300000003 8888 20061111 20081223
    300000003 9999 20081224 29990101
    400000004 1010 20081212 20090614
    400000004 1011 20090616 29990101

    The combination (ServiceID+IMEI) is unique through out table T1.

    Also,for each ServiceID there is only one enddate with 29990101.

    Table T2 contains following set of records:
    ServiceID IMEI startdate enddate
    100000001 1111 20081105 29990101
    100000001 3333 20081224 29990101
    200000002 6666 20081203 29990101
    300000003 9999 20081224 29990101
    400000004 1010 20081212 29990101

    Now, i need to delete the records in from T1 that match with ServiceID & IMEI of table T2.
    Also, i need maintain RECORD CHAINING after deletion,

    So,After deletion T1 should look like

    ServiceID IMEI startdate enddate
    100000001 2222 20081128 20090710
    100000001 4444 20090711 29990101
    200000002 5555 20081122 20090607
    200000002 7777 20090608 29990101
    300000003 8888 20061111 29990101
    400000004 1011 20090616 29990101

    I just don't know how to go about it.

    The problem comes in two scenarios

    1)when a record between to records have to be deleted like the following.

    Before:
    100000001 2222 20081128 20081223
    100000001 3333 20081224 20090712 (delete)
    100000001 4444 20090711 29990101

    After:
    100000001 2222 20081128 20090710
    100000001 4444 20090711 29990101

    2)when a scenarion like the one below occurrs.

    Before
    300000003 8888 20061111 20081223
    300000003 9999 20081224 29990101 (delete)

    After:
    300000003 8888 20061111 29990101

    I thought of using a cursor, but due to enormous data,it will not work.

    Can anybody help !!

    Iz am in urgent need of this solution.

  2. #2
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Yes, a cursor is not an option here. ASIQ is a warehousing solution, with a very fast import capability. This sort of processing is better done in the source database (ordinary SQL) an then re-imported. ASIQ/SQL has limitations, it was never intended as a update-intensive product.

    Another approach is: create a new table T1_new by joining T1 and T2, and excluding the rows you want to delete; then drop T1; rename T1_new to T1.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  3. #3
    Join Date
    Sep 2009
    Posts
    7

    "RECORD CHAINING" will not be maintained in Second approach !!

    Quote Originally Posted by Derek Asirvadem
    Yes, a cursor is not an option here. ASIQ is a warehousing solution, with a very fast import capability. This sort of processing is better done in the source database (ordinary SQL) an then re-imported. ASIQ/SQL has limitations, it was never intended as a update-intensive product.

    Another approach is: create a new table T1_new by joining T1 and T2, and excluding the rows you want to delete; then drop T1; rename T1_new to T1.
    Hi Derek, problem is that i have to work this out in IQ, no go situation.

    And the second approach will not take care of "RECORD CHAINING" that has to be maintained in T1 after deletion..

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by suhel_2112
    I am dealing with the following Business scenario:

    I have two tables T1 and T2.(I Production these table contain 100 Million Records or more)
    ...
    I thought of using a cursor, but due to enormous data,it will not work.

    Can anybody help !!
    I'm afraid your description of the problem is difficult to understand so I can't provide you with any SQL but couldn't you just repeatedly process a subset of the data until all the data is treated. This process could continue running to process any new records as they appear in future.

    Code:
    Do
       Identify say 100k records that need to be processed
       Do your processing on just these records
       Sleep for 30 minutes
    Until forever
    You'll have to monitor the time it takes to identify which records can be deleted and how long it takes to process them and adjust the 100k figure up or down to suit. If you run this process only during quieter periods then it shouldn't interfere with batches etc.

    Using table names and fields with no meaning make it hard for us to understand the sense in what you're doing ie T1, T2, EMEI. Putting various words into upper-case (ie RECORD CHAINING) doesn't make these words any easier to understand.

  5. #5
    Join Date
    Sep 2009
    Posts
    7

    Business concept

    Quote Originally Posted by mike_bike_kite
    I'm afraid your description of the problem is difficult to understand so I can't provide you with any SQL but couldn't you just repeatedly process a subset of the data until all the data is treated. This process could continue running to process any new records as they appear in future.

    Code:
    Do
       Identify say 100k records that need to be processed
       Do your processing on just these records
       Sleep for 30 minutes
    Until forever
    You'll have to monitor the time it takes to identify which records can be deleted and how long it takes to process them and adjust the 100k figure up or down to suit. If you run this process only during quieter periods then it shouldn't interfere with batches etc.

    Using table names and fields with no meaning make it hard for us to understand the sense in what you're doing ie T1, T2, EMEI. Putting various words into upper-case (ie RECORD CHAINING) doesn't make these words any easier to understand.

    Hi mike,

    The reason behind using names T1 and T2 is to deal with client data confidentiality.

    Let me break it down.
    The client here is a Telecom operator.

    T1 and T2 are profile tables.
    That is , they record ,from which location a service is being used.

    T2 contains a profiles that have to be removed from T1.

    These have quite a few columns that deal with location, which i have not mentioned in the E.G as they are not used in query.

    The columns have following meaning.
    ServiceID : A service mean u r a POST paid costumer or a PRE Paid Costumer for the Telecom operator.

    IMEI : every mobile phone has a unique number[hard ware serial number] .This is unique globally hence used to identify customers.

    combination of (ServiceID+IMEI) help determining which customer is using which service.

    E.g :
    lets say ,you r a pre paid customer who is currently in UK.
    a record for serviceID 100000001 is inserted into T1 as follows.

    ServiceID IMEI startdate enddate location
    100000001 2020 20090922 29990101 2420000 (UK)

    enddate =29990101,
    meaning record contains latest data for a particular ServiceID there can be only 1 record with 29990101.


    Next time , if somebody in France uses the service then previous record is closed and a new one is inserted into T1.

    ServiceID IMEI startdate enddate location
    100000001 2020 20090922 20090923 2420000 (UK)
    100000001 3030 20090924 29990101 2520000 (FRA)

    RECORD CHAINING - > you can observe the end date of 1 st record and start date of 2nd record.

    The problem in the business scenario is that suppose ,

    Lets bring uk and france into picture again.also lets put in italy.

    ServiceID IMEI startdate enddate location
    100000001 2020 20090922 20090923 2420000 (UK)
    100000001 3030 20090924 20090925 2520000 (FRA)
    100000001 4040 20090926 29990101 2620000 (ITL)

    If i have to remove FRA, i can simply delete.
    ServiceID IMEI startdate enddate location
    100000001 2020 20090922 20090923 2420000 (UK)
    100000001 4040 20090926 29990101 2620000 (ITL)

    BUt the RECORD CHAINING will break.

    RECORD CHAINING - > you can observe the end date of 1 st record and start date of 2nd record. these have to be consecutive , as below

    ServiceID IMEI startdate enddate location
    100000001 2020 20090922 20090925 2420000 (UK)
    100000001 4040 20090926 29990101 2620000 (ITL)

    This is the business aspect of the business problem.............

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527

    Wink

    This is my guess at how to do it. There's probably a bunch of syntax (and possibly a few logic) errors in there but it might give you a few ideas. I can't test it so don't run it on live data!
    Code:
    -- get required info
    set rowcount 1000
       select T1.ServiceID, T1.IMEI, 
              startdate = min( T1.startdate ), 
              prev_enddate=max( T1.enddate )
       into   #tmp
       from   T1, T2
       where  T1.ServiceID = T2.ServiceID
              and T1.EMEI = T2.EMEI
              and T1.enddate != 29990101
       group by T1.ServiceID, T1.IMEI
       having count(*) > 1
    set rowcount 0
    
    -- delete middle rows
    delete T1
    from   T1, #tmp
    where  T1.ServiceID = #tmp.ServiceID
           and T1.IMEI = #tmp.EMEI
           and T1.startdate > #tmp.startdate
           and T1.enddate != 29990101
    
    -- alter end date of initial record to plug gap
    update T1
    set    startdate = #tmp.prev_enddate
    from   T1, #tmp
    where  T1.ServiceID = #tmp.ServiceID
           and T1.IMEI = #tmp.EMEI
           and T1.enddate = 29990101
    I'd set the rowcount to 1000 but you'll need to adjust this. This code will also need to be called every few minutes during the quiet periods of the day. You might need a transaction around the delete and update statements or you could just use a permanent table rather than a temp table and then only add new ids when the old set have been fully processed - obviously you'll need to delete these ids in this new permanent table when done.

    EDIT : improved the code and added some ideas.
    Last edited by mike_bike_kite; 09-22-09 at 10:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •