Hot answers tagged

106

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 ...


41

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. +-----------------+---------...


12

Use the PasswordLastSetTime option of the LOGINPROPERTY. SELECT LOGINPROPERTY('YourLoginName', 'PasswordLastSetTime');


8

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 (...


7

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

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 ...


5

Without getting too involved in specific details on the language required to do this in Sybase SQL Anywhere 8, which is now more than 15 years old, I'd recommend something like the following: create a table on the laptop database that contains a column which uniquely identifies rows that have already been exported to the main database. the export function ...


5

What you see in your execution plan is Sybase performing a DISTINCT operator. When you do a SELECT DISTINCT in Sybase it first builds a Worktable (as you can see in step 1 of your execution plan) and then it selects sorted data removing duplicates from that worktable (which is what you see in step 2). This is documented The sort algorithm doesn't appear to ...


4

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)


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 ...


4

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 ...


4

The best thing to do to resolve your question is to run the lsblk command. Without any arguments, it gives a great tree view of the system's block devices (see below). Check the man page (lsblk(8)). Then, use df -h on your file of interest (in this example, it's the MySQL server). df -ah gives more information - in this case, the mount point and the ...


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

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

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 disconnect ...


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

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

the batch will fail. do all the table creates first, then do all the foreign key constraints AFTER all the tables are created. OR create the tables with constraints in the correct ORDER so that constraints don't fail.


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

Note that the approach shown above (using sysconstraints and sysreferences) will only work if you have referential constraint. If you have just a foreign key defined but without constraint, you will not see it there. To get all foreign keys related to a given table, you can use syskeys: select rtrim(object_name(k.id)), rtrim(substring(col_name(k.depid, ...


2

Another aproach is "sp_depends" Sybase URL: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs68.htm


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

Looking at your use case, it looks as if you want a stored procedure, so here's one I wrote for you: CREATE PROC the_script ( @param1 int = null, @param2 int = null ) AS BEGIN select col_1, col_2, etc from table where cond1 > @param1 and cond2 < @param2 END Now, being in isql, you can run it: exec ...


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

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

I was unable to fix the database in place, but I did manage to solve this misconfiguration by: Creating a new database with my optimal device layout Migrating the schema via DDL generation scripts using ddlgen.bat Using bcp to migrate the table data Note: You could use Sybase Central's DDL generator, but it doesn't include table's indexes for some reason....


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 ...



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