Tag Info

Hot answers tagged

68

No. TRUNCATE and DROP are almost identical in behavior and speed, so doing a TRUNCATE right before a DROP is simply unnecessary. Note: When I first posted this answer, there were several other highly rated answers -- including the then-accepted answer -- that made several false claims like: TRUNCATE is not logged; TRUNCATE cannot be rolled back; TRUNCATE ...


31

Testing TRUNCATE then DROP vs just doing the DROP directly shows that the first approach actually has a slight increased logging overhead so may even be mildly counter productive. Looking at the individual log records shows the TRUNCATE ... DROP version is almost identical to the DROP version except has these additional entries. ...


6

It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea: can't have filtered foriegn keys can't FK to constants can't have multiple parents Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I ...


5

Don't know about Sybase but in SQL Server you can do like this. select * from yourtable where value not in (select N from (values (@var1), (@var2), (@var3), (@var4), (@var5)) T(N) where N ...


5

I don't actually have a sybase system to test with, but according to http://dcx.sybase.com/1200/en/dbreference/sa-split-list-sysproc.html the following solution should work: SELECT * FROM table AS T JOIN sa_split_list('1234,23,56,576,1231,567,122,87876,57553,1216') AS L ON T.id = L.row_value; If the id_list is actually stored in another table ...


4

First: This isn't easy to implement and it's going to break very easily (by which I mean performance is going to be horrible, and it's a nice little route for SQL injection attacks if you're not very very careful). I strongly advise you to re-think what you're doing because there has to be a better way. Second: The question as it stands doesn't actually ...


3

Rows are not returned in any specific order in SQL (in reality they are likely to be in the same order most of the time) so without an ORDER BY any of the rows could be returned first. So it is non deterministic because this first record can change each time you run the query. Wrapping it in a block doesn't make the answer deterministic, it just hides the ...


3

Short version: It depends. Generally spoken Sybase SQL Server is smart enough to do things the fastest way, though. Long version: Sybase's query processor is, at it's core, very similar to the one used in MS SQL Server. It will create worktables (internal temporary tables; not visible to the user) if the result set is sufficiently large to overflow ...


3

The transaction log is where Sybase stores all changes made to a database. It can be dumped separately from the database. Dumping the transaction log basically provides some kind of incremental backup functionality. The first thing you need to figure out is whether you need the transaction log on this database i.e. is it OK for you if all data created since ...


3

Ok ! Let me describe how this issue was solved: 1) There was definitely a corruption issue caused by a problem with vmware tools on solaris 10. When the network interface had high transfer/load operations (sample: copy of a 2 GB DB ....), it just stoped working, in the middle of the operation. To put the interface working again, I had to ...


3

Much simpler: CONVERT(varchar(8),GETDATE(),112) You can find a full list of CONVERT styles in Sybase BOL, although the explanation of each style isn't particularly clear (and is actually flat wrong for style 12 & 112). The Complete Sybase ASE Reference Guide includes a full list of styles with examples. (Please note that the complete guide is externally ...


3

As Sybase also has a JDBC driver any Java/JDBC tool should work too (at least the basic stuff). These are the free JDBC based tools that I know of: DbVisualizer (not open source, but has a free version) ExecuteQuery SQL Developer That's not the one from Oracle! It's not open source but free as well SQL Workbench/J Squirrel has already been mentioned. ...


3

Recently I had a similar problem … Here goes all the required steps with comments … ! [On Source] ============================================================================ 1) Checks on Source a. Run (on solaris prompt) On source machine Execute: uname -a In my case the output was: SunOS clusterz1 5.10 Generic_142900-03 ...


3

Frian, I discussed this algorithm with a colleague last week. I haven't had a chance to test it yet, but we have confidence in it. 1) Convert the start and end times into a number of hours. This can be the HOURS() function or DATEDIFF. The baseline can be anything, but MIN(TimeStampStart) would be most convenient. Round TimeStampStart up and ...


3

I would take answer by Micheael Green a step further and suggest generating a Numbers table. It will help many other algorythms as well. Another handy table is Calendar table with every date for +/- 20 years. You can get numbers from ID column of such table as well. Here is a query I came up with. You can easily wrap it into SP to TVF. It works for me. I ...


2

It's bugging me that this question doesn't have at least one answer. So... You could use a mix of primary/foreign keys within the table per Joe Celko. Alternatively, you might want to consider a closure table. Generally, the table referencing itself seems to be the most common method for handling this. Of course, you would have to consider looping. ...


2

Been a while since I've worked in sybase but (from memory) the following sql should point you in the right direction: select * from sysobjects so inner join syscolumns sc on so.id = sc.id where sc.name = 'field name' you can also check out the sybase books online (system tables)


2

I have seen this before. According to the Sybase ASE Documentation: Adaptive Server Enterprise implements dynamic SQL using temporary stored procedures. A temporary stored procedure is created when a SQL statement is prepared, and destroyed when that prepared statement is deallocated...[a]s a consequence of this implementation, an application accessing ...


2

If you can take the hit on performance, enable tracing for a "typical" session (set tracefile "/some/path/" for some_spid), and using set show_sqltext on you can see all the SQL executed. Then you will need to parse the output looking for stored procedures. Of course this approach cannot give you 100% confidence (e.g. maybe you have a batch job that runs ...


2

In Oracle, you can restore onto an OS/CPU with the same endianness in most cases. This can be done with RMAN, the standard Oracle backup and restore tool, but it might be worth it to investigate options such as DataPump or a Linux VM on AIX (but I don't know how that would deal with endianness). x86 is little-endian and POWER is big-endian, so this would ...


2

This should work the same. select ... from yourtable T1 JOIN ( SELECT value FROM yourtable§ EXCEPT SELECT @var1 EXCEPT SELECT @var2 EXCEPT SELECT @var3 EXCEPT SELECT @var4 EXCEPT SELECT @var5 EXCEPT SELECT @var6 ) T2 ON T1.value = T2.value where ...


2

Take a look at the -zr switch on the server, which turns on "request level logging". Start the server with -zr all -zo reqlog.txt and then look at reqlog.txt after some queries have been executed. It's fairly verbose but should list the SQL that's being executed. Disclaimer: I work for Sybase in SQL Anywhere engineering.


2

The dump process happens in 3 phases: All data and log pages are dumped. Data pages modified during phase 1 by non-logged operations are dumped. Log pages are dumped again. Everything happening until end of phase 2 are contained in the dump. But the changes performed during phase 3 will be missing. Of course phase 3 is pretty fast, so the chances are ...


2

If you subtract 1 from FieldNum and divide the result by 3 using integral division, you will get the following results for FieldNum values of 1 till 6: FieldNum | (FieldNum - 1) / 3 ---------+------------------- 1 | 0 2 | 0 3 | 0 4 | 1 5 | 1 6 | 1 And if you replace ...


2

Assuming that the rows come in three, with same EntityID and consecutive FieldNum values, and the first row has a date in the MemoText, this will work, too: SELECT eq0.EntityID , eq0.Memotext AS [Date] , eq1.Memotext AS Name1 , eq2.Memotext AS Name2 FROM EQ AS eq0 JOIN EQ AS eq1 ON eq1.EntityID = eq0.EntityID AND ...


2

No. Fast BCP is an unlogged operation. Sybase Replication Server relies on scans of the Transaction log for data to be replication. The Rep Agent thread scans the transaction log in the Primary Database (PDB) for commits. Once a committed transaction is found in the Transaction log, the SQL statements are forwarded to the Replication Server. If an action ...


2

Having no experience with Sybase, I suppose I would start by exporting the database schema into a text file, then go through and modify the CREATE TABLE statements manually until I am able to load the schema into MySQL. Next, dump the data from each sybase table into CSV format file and then in the MySQL database, use LOAD DATA INFILE. Depending on your ...


2

You can use the Sybase utility ddlgen to export the database structure, schema, objects, etc. from there you will have to go through the script files that are generated, and figure out what will run and what won't run. Data can be exported by using the Sybase bcp utility, with whatever delimiters are necessary to import into your new database. Both of ...


2

Solution is to create procedure with use of dynamic query: CREATE PROCEDURE view_with_parms( in str1 varchar(255), in str2 varchar(50) ) RESULT( myID integer, someName varchar(255)) BEGIN EXECUTE ( 'select myID, someName from ' || str1 || ' order by ' || str2 || ' asc' ); END then: select * from view_with_parms('test_table','myID'); Tested ...


2

Two things sprint to mind: The process could be partly CPU bound on a single thread. You don't state what you CPU resource is, but if you have a 4-core arrangement then it coudl be that the ~25% use you are seeing is a single thread bouncing between the cores. Similarly with 8 cores it could be using 2x parallelism so two cores worth of CPU resource which ...



Only top voted, non community-wiki answers of a minimum length are eligible