tag:blogger.com,1999:blog-497787815177352569.post3029955597540333909..comments2024-01-03T08:55:04.827-05:00Comments on SQL Anywhere: UNLOAD COMPRESSED In ActionBreck Carterhttp://www.blogger.com/profile/15975598564711761434noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-497787815177352569.post-54366649925033578802010-12-24T16:49:53.822-05:002010-12-24T16:49:53.822-05:00AWESOME! ...it works! ...just have to get the righ...AWESOME! ...it works! ...just have to get the right \\\\\\\\ number \\\\\\\\\\\\\\\\\ of \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ backslashes :)<br /><br />(don't worry, YOU had them right, I'm just doing FORWARD TO via EXECUTE IMMEDIATE, lotsa b\a\c\k\s\l\a\s\h\e\s required)<br /><br />Stay tuned for another blog post... gonna have to rename this website to unload.blogspot.com<br /><br />BreckBreck Carterhttps://www.blogger.com/profile/15975598564711761434noreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-37639753144968988332010-12-24T10:25:01.354-05:002010-12-24T10:25:01.354-05:00I shouldn't post comments until I've thoug...I shouldn't post comments until I've thought everything through, right? The name after \\.\pipe\ does matter somewhat. If the one-and-only internal pipe is in use with another name, the LOAD or UNLOAD will block until it is no longer in use. So, either wait for both the LOAD and UNLOAD to complete before issuing the next pair of statements or use a unique name for each pair. Otherwise, you could end up with two UNLOADs writing into the same pipe and one LOAD might see the data from both UNLOADs (one file concatenated after the other).John Smirnioshttps://www.blogger.com/profile/17831427682756190054noreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-28731853452522547992010-12-24T10:07:42.192-05:002010-12-24T10:07:42.192-05:00One other caveat occurs to me. If a LOAD TABLE ref...One other caveat occurs to me. If a LOAD TABLE refers to a named pipe, it will not be recoverable unless you use CONTENT LOGGING or ROW LOGGING. Otherwise, the LOAD statement that gets logged will refer to the pipe path and that's not good. For a reload, it's not a big problem since the database gets checkpointed and the log is thrown away. I'm not sure what should be done in "production" -- it's so nice to be able to do the load without logging. That issue needs to be addressed too.John Smirnioshttps://www.blogger.com/profile/17831427682756190054noreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-19871164561267597362010-12-24T09:53:52.512-05:002010-12-24T09:53:52.512-05:00Boy, do I have suggestion for you. You might call ...Boy, do I have suggestion for you. You might call it 'undocumented' but it's really something you could have tried on your own. Due to historical reasons, though, it's both better and worse than you would think.<br /><br />The suggestion is to use a named pipe to carry the data. Supposing that both the old and new databases are on the same database server, open one connection to each database and have each connection be used by a separate thread. On the old database, issue an UNLOAD .. TO '\\\\.\\pipe\\data'. That's just a standard Windows named pipe path. On ANOTHER THREAD and using the connection to the new database, *simultaneously* run LOAD TABLE ... FROM '\\\\.\\pipe\\data'. The data will pass straight between the two statements without needing intermediate data files. It's what dbunload does when rebuilding a database.<br /><br />Okay, so normally that's just using your OS facilities and not an undocumented feature of SA. It's a pretty common technique for UNIX hacks who are familiar with mkfifo(). Here's the caveat, though. Due to historical reasons, SA recognizes any path that starts with \\.\pipe as a local named pipe and doesn't actually open an OS named pipe. Instead, it bypasses all that and shuffles the data via internal buffers. There can only be one such pair of operations in progress at a time since there is only one internal buffer for this purpose. Everything after \\.\pipe is ignored.<br /><br />There are ways to use real OS named pipes but it's kludgy since at some time in the past we claimed the OS's pipe paths as our own. That should get cleaned up and documented for users who wouldn't think about using named pipes.<br /><br />Another caveat: if you do use a real OS named pipe (which I haven't showed you how to do), the server is at the mercy of the producer (which in this case is the UNLOAD but could an arbitrary program) to close the pipe. Otherwise, LOAD will wait indefinitely for more data.<br /><br />-john.John Smirnioshttps://www.blogger.com/profile/17831427682756190054noreply@blogger.com