Both SYSIBM.ROUTINES and SYSCAT.ROUTINES are actually views over the "real" catalog table, SYSIBM.SYSROUTINES. You can grab the SQL definition for each view like so:

SELECT TEXT
FROM SYSCAT.VIEWS
WHERE VIEWSCHEMA = 'SYSIBM' AND VIEWNAME = 'ROUTINES';

SELECT TEXT
FROM SYSCAT.VIEWS
WHERE VIEWSCHEMA = 'SYSCAT' AND VIEWNAME = 'ROUTINES';

I won't post the results here as I've no idea if that's considered proprietary by IBM (it is technically source from DB2 itself after all). Anyway, if you look through the view source (you'll want to re-format it first with some SQL lint-like tool!) you'll notice several things:

SYSIBM.ROUTINES excludes functions with type "T" and "R" (i.e. table and row functions, so it only includes scalar functions, aggregates, and procedures). This probably explains a lot of the difference.
SYSIBM.ROUTINES excludes routines in the schema SYSFUN. This probably explains the rest of the difference as there's 100+ routines in SYSFUN.
SYSCAT.ROUTINES only excludes routines from the SYSIBMINTERNAL schema (there's only a few of these - not sure if all/some/none show up in SYSIBM.ROUTINES given the restrictions above).
There's a load of joins in each view but I'm pretty sure they're all 1-to-1 and aren't affecting the cardinality of the result which is dictated by SYSIBM.SYSROUTINES (most of those joins are for pulling in things like the result type or collation details).

Some background:

The tables starting with SYS in the SYSIBM schema are the real catalog tables that everything is based upon. Generally I wouldn't bother with these as they're undocumented, and a bit of a pain to work with (however in things like DB2 for z/OS which lacked SYSCAT last time I worked with it, they're the only tables you've got).
The views not starting with SYS in the SYSIBM schema have a structure awfully similar to the standard INFORMATION_SCHEMA. Something probably uses them as such (maybe some JDBC methods, or the catalog functions in various drivers?) but as they're otherwise undocumented it's difficult to know exactly what they include/exclude without looking at their view definitions (using the trick above).
The views in the SYSCAT schema are the really useful ones. They're well documented and generally quite easy to work with (sadly they're also DB2 for LUW specific).

In other words, I'd generally treat the SYSCAT results as "gospel" but if you want to understand exactly what they're showing then you need to dive into their source.


Making a recursive CTE like query, but for many records

Assuming the original table design is somewhat like this:

CREATE TABLE dbo.Area
(
    RowID integer PRIMARY KEY,
    GroupID  integer NOT NULL,
    ParentID integer NULL,
);

Sample data:

INSERT dbo.Area
    (RowID, GroupID, ParentID)
VALUES
    (1, 1, NULL), -- Root
    (2, 1, 1),
    (3, 1, 2),
    (4, 2, NULL), -- Root
    (5, 2, 4),
    (6, 2, 5),
    (7, 2, 6);

The following recursive CTE query finds roots for the RowID list given in the anchor:

WITH R AS
(
    SELECT
        A.RowID,
        A.RowID AS OriginalRowID,
        A.ParentID
    FROM dbo.Area AS A
    WHERE
        A.RowID IN (3, 7)

    UNION ALL

    SELECT
        A.RowID,
        R.OriginalRowID,
        A.ParentID
    FROM dbo.Area AS A
    JOIN R
        ON R.ParentID = A.RowID
)
SELECT
    R.OriginalRowID,
    R.RowID
FROM R
WHERE
    R.ParentID IS NULL
ORDER BY
    R.OriginalRowID
OPTION (MAXRECURSION 0);

The output shows each supplied RowID and the root for that group:


Which stored procedures are using a specifc index

I couldn't quite get Jonathan's XML query to pull this out for me, but I was able to get it by parsing showplan_text the simple way. This is using AdventureWorks2012 and after issuing queries against Sales.SalesOrderHeader:

DECLARE @Index SYSNAME = N'PK_SalesOrderHeader_SalesOrderID';

SELECT t.[text], s.execution_count, CONVERT(XML, p.query_plan)
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_text_query_plan
(
  s.plan_handle, s.statement_start_offset, s.statement_end_offset
) AS p
CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
WHERE p.query_plan LIKE '%Index="\[' + @Index + '\]"%' ESCAPE '\';

Results:

To get stored procedure names specifically (for those queries that are not issued ad-hoc), you'll want to make sure you are in the context of your database, and then add this column to the output list of the above query:

, obj = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid))
  + '.' + QUOTENAME(OBJECT_NAME(t.objectid))

This column will be NULL for any ad-hoc queries.

Once the query plan is in the results pane, you can of course click on the XML to load the graphical plan. And then if you have SentryOne Plan Explorer and the SSMS add-in installed, you can right-click the graphical plan to view in Plan Explorer.

Note that the query won't return results for queries issued from another database, so you should repeat the query in the context of any database that might be used in conjunction with the target database.

In contrast, Jonathan's query should be able to retrieve queries in the plan cache that reference any index with the specified name (there may be multiple). You can further filter to find references to only this specific one, even if those queries originated in other databases.


MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations

To solve the error message ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER you can use one of the possible solutions:

1) Increase MaxNoOfConcurrentTransactions even more until it works (note it take more resources (RAM))

2) Select the data from innodb into an outfile and load infile into
cluster (will be faster than 2).

Note: you faced this error message  since there is not enough resource on Dedicated for the cluster evnironment.


Postgres reported table / db size varies wildly from dump size

Note that unless your application is going out of its way to use the large objects interface, then it is not storing those columns as LOBs. Normal columns can store up to 1gb using TOAST (The Oversized-Attribute Storage Technique).

In addition to storing the data out of line, it may be stored compressed. Although you don't say so I assume the dump you are composing with is uncompressed, which could account for most of the size difference.


Refactoring a very large database table and having join over millions of records

Don't  "select *", then the other 50 fields won't be a burden. Better optimize the critical query.


HStore vs. multiple tables with inheritance - do I have a good use case for HStore?

That would certainly eliminate the need to have JOINs but I feel like it would be logical to have individual experiments be separate in some way. Also, it will be very common that one experiment is requested in its entirety

Both of those are good arguments for using table partitioning. PostgreSQL implements this with inheritance and constraint exclusion.

  I’ve thought about having additional tables for storing meta-data/annotations relating to individual data points in individual experiments. Some of this would fit very nicely into the HStore experiments table

Nothing stops you having an hstore field for annotations, or a separate join table of hstore fields for them.

  Different users will be inputting experimental data, I would use schema for this

Why? Surely the same argument applies, that you'll want to do cross-user aggregation too?

I'd just partition by experiment and have the user ID as part of the table's key.

  There will be different experiment types – I think the best way to handle this would be storing these in entirely different databases

Why?

Do you ever think you might need to query across them or aggregate them? If so, do not store them in different DBs.

If they store similar kinds of data, just have them in the main table, and have some optional columns.

If they store mostly different kinds of data (mostly or entirely different columns) then use different tables in the same DB.


Moving Clustered Index to new filegroup results in excessive filegrowth

I believe I've found my own answer to this question.  When setting SORT_IN_TEMPDB on, the filegroup no longer expands unpredictably, and a shrink with truncate only is successful in recovering space which is all trailing space.  If trying this, note that this could result in additional space requirements for TempDB.


Oracle RAC: Can two transactions share an SCN?

Two transactions won't share an SCN barring a bug in Oracle.

Exactly how Oracle ensures this varies by version, platform, and configuration settings.  For example, back in the Oracle 9.2 days, there was by default a non-zero max_commit_propagation_delay which meant that a row that was inserted on one node and committed might not be immediately visible to a query on the other node.  In that configuration, Oracle could use a Lamport SCN generation algorithm where SCN's were generated in parallel without a need for internode communication.  In 10.1 and later, a broadcast on commit strategy (link is to a PPT presentation from Julian Dyke which is excellent but may be bigger than you'd expect) is used instead and max_commit_propagation_delay defaults to 0 (no idea what happens if you use 10.1 and set a non-default max_commit_propagation_delay-- that might well flip back to Lamport).


Speed up query that have joined tables and function calls

If you mark the functions as WITH SCHEMABINDING, then this may help - particularly if the function doesn't access any tables.


Can't return data from SQL/Plus and Toad but can from SQL Developer

Sounds like the network buffer is being filled but not flushed.  SQL*Net performance in this area is often managed by setting tcp.nodelay=yes in sqlnet.ora; also look at the send_buf_size and recv_buf_size parameters.

SQL*Dev working and not SQL*Plus are what pointed me to this type of troubleshooting approach.  SQL*Developer uses jdbc behind the scenes to do its fetching.

hth!


Better way to manage data integrity, without having too many composite FK's

Two composite FKs including the otherwise redundant column class_id are probably the most elegant and efficient solution to enforce that users can only enroll in courses of their class. I see no better alternative.


Clustered index in ETL (drop - insert - create?)

The perfmon counters [\SQLServer\Databases(*)\Bulk Copy Rows/sec] can help determine whether the SSIS package is performing bulk insert.  You want bulk insert, especially if the underlying table is ever converted to a clustered columnstore.
In your ETL, I'm assuming that each ETL involves X+Y rows, where X is a number of rows that are updates and thus are deleted from the ~130 million row target table and Y are net new rows.  A delete is performed on the target table and then all X+Y rows are inserted.
(It's also possible that this ETL simply inserts Y new rows with each iteration, and deletes X old rows past some cutoff point - eg deletes all rows past 30 days old.  That makes some of this designing/planning easier especially if the ~130 million row target table stays fairly stable in size over time, rather than consistently growing over time.)
It's very important to know whether this schema and process is to be optimized for the ETL, optimized for the non-ETL workloads, or balanced between them.
For some system and workload combinations, ETL is done within an acceptable amount of time just about however it's done.  In such a case, optimization can almost completely focus on the non-ETL workloads.  The maxims then are "write the data how it will be read" and "keep it simple".  No need to worry about the page split behavior (and transaction log ramifications) of a clustered index vs heap, or that clustered index insert will most likely always be a serialization point in the ETL.  It all becomes: if there's a key - either single or combination - that is meaningful for the reporting queries and with a significant number of the table columns, that can be considered as a candidate for the clustered index key.
Fragmentation of both the clustered index (if adopted) and the nonclustered index can be managed according to the same considerations. (But index maintenance is usually more of a consideration for non-ETL workloads than ETL workloads.)  I usually focus on index reorganizations rather than rebuilds, because what I really care about is keeping the pages pretty full.  One reason I like looking at it from that perspective: it is much less expensive to keep track of over time.  I can just watch how many mb each million rows is on average, before and after a reorg.  That can be measured fairly inexpensively, in comparison to using sys.dm_db_index_physical_stats.  There are two inherent advantages to rebuild vs reorg: an index rebuild automatically includes a fullscan stats update, and an index rebuild can be performed in parallel while a reorg is always serial.
Now, what if ETL performance optimization takes precedence over everything else?  Then you don't want a non-clustered index on the heap, unless it's used to make the deletes faster by making a more efficient plan available.  It's possible that the added cost of maintaining that non-clustered index during the ETL inserts is a significant drag on ETL performance, even if it speeds up the deletes.  In that case, consider creating the non-clustered index before the deletes.  And dropping it immediately after the deletes. That way it never has to be maintained with REORG or REBUILD.  And the deletes get the benefit while the inserts don't have to worry about the cost.  But that has to be measured in order to know if it's benefecial.  And the measurement needs to be re-visited as the table grows to 2x, 3x size etc.  Or if the number of deleted rows in each ETL changes signficantly.
If ETL performance takes precedence over everything else, probably don't want a clustered index on that table, either.  Here's one of the performance pitfalls of heavy insert activity into a clustered index: if its estimated at more than 100 rows, its going to be sorted before the insert.  Depending on the size of the insert, the maximum memory grant, and the grant that insert query recieves that may mean tempdb spill - and a big increase in elapsed time for the insert.
All right.  Chances are, this system and workload hint toward performance optimizations needed for both ETL and non-ETL workloads.  Maybe the non-clustered index is used both for the ETL deletes and by reporting queries?
In that case, consider dropping it before the inserts, and creating it after the inserts.
This blog post from Paul White is one I think about a lot.  He describes wide, per-index plans and narrow, per-row plans here.
Optimizing T-SQL queries that change data
26 January 2013
https://www.sql.kiwi/2013/01/optimizing-t-sql-queries-that-change-data.html
Maybe keeping the non-clustered index in place for both deletes and inserts is the best choice for your needs.  Maybe creating before the deletes, and dropping after the deletes.  Maybe dropping before the deletes, and creating after the inserts.  It depends which query plans actually use the index, and the relative cost of creating the index vs keeping it around for the activity.
Similarly, maybe a clustered index will be beneficial to the deletes and to the reporting queries (although if the clustered index is meaningful to the deletes the non-clustered index is less likely to be meaningful to the deletes).  It'll be a net cost to the inserts, though, compared to a heap.
And... yeah, I agree that in general creating a clustered index and then dropping the clustered index in order to manage fragmentation/average fill in the heap is not something to be done on a regular basis.  I'm much more in favor of making sure TABLOCK/TABLOCKX hints are used as needed in delete statements, so page locks are taken and pages emptied by the delete can be deallocated from the heap.
If a clustered index will be created and dropped in order to manage fragmentation of a heap as a once-in-a-while-when-really-needed thing, I recommend taking such an action at a time that the NCI(s) can be dropped before the clustered index is created, and then NCI(s) created again after the clustered index is dropped. Because creating a clustered index on a heap with NCIs will rebuild the NCI(s).  And dropping the clustered index will rebuild the NCI(s) again.  Droppng the NCI(s) beforehand and creating the NCI(s) again at the end reduces that rigamarole by about half.
Hopefully, rather than creating a clustered index just to drop it again, if once-in-a-while intervention is needed on the heap, it can take the form of ALTER TABLE... MOVE.  For that operation, too, I recommend dropping NCI(s) beforehand, and creating NCI(s) afterward.  I like having individual control over the NCI rebuilds rather than having them take place automatically due to an action taken on the underlying heap.
One important variable to keep in mind in evaluating these options: the recovery model of the database in question.  If transaction log operations are a concern either for performance or transaction log space and the database is in simple recovery model, engaging minimally logged operations wherever possible may be a big gain.  If the database is in full recovery model and the txlog is a concern, page splits in a clustered index (or in the NCI for that matter) become a bigger deal.  For full recovery model, the benefit of sort_in_tempdb for index create or rebuild operations increases (unless tempdb is more of a sore spot for the instance than the txlog is for the database).


I don't have any insider knowledge of how Google Docs is implemented.  However, if you look at things like Windows file permissions, this type of thing usually works on the these principles:

Inheritance - To grant access to many objects with the same scope, grant the access at the highest level (e.g. folder, not file) and all children of the granted node get the same permission as the parent.
Group and Individual Privilege - Rights can be granted to groups and to individuals.  Individuals can belong to multiple groups and you might be in more than one that has access.  Alternatively, access can also be granted to an individual.
Explicit Grant - Assume access is denied unless there is an explicit grant in place.  The explicit grant can be to the individual or to one or more groups that the individual is a part of.  The grant can be to the specific resource or the resource can inherit the grant from an ancestor.  However, no access is allowed unless there is a qualifying grant somehow.

By applying these three principles you can manage the amount of data that must be maintained to control access to resources.

EDIT:

See my answer to a similar question over on Stack Overflow.  The basic idea is to map resources and principals into hierarchies or networks.

The resources (documents) will probably fit neatly in an un-level hierarchy of directories and documents.  How you model the actor (user/group) depends on your business rules.  Generally groups are one level deep, but users can be in multiple groups.  This means you will probably want a sub-typing approach to principals such that they can be users or groups.  You will have an intersection table (m:n) between principals to indicate membership.

To make the traversal of the resource hierarchy more efficient, you might consider one of the common relational hierarchy tricks.  My usual favourite is "visitation numbers".  See also my answer to this question for more about working with visitation numbers.

Consider the following (mostly logical) ERD:

Using this type of model, the query to find out if a user has access to a given document is a matter of joining Membership to Resource through Grant where the matched left and right are greater than or equal to the left and right of the resource in question.  This gives you the answer based on group membership.  To get direct individual grants you would do the same thing but leave out Membership and just use the ID from Individual to filter the Grants.


Query Last Value in Fixed Length Array With Empty Values

SELECT distinct Name, loc
FROM (
  SELECT  *, last_value(Location) over (partition by Name) as loc
    FROM (SELECT Name, unnest(Location) as Location FROM table)  x) y;

Should answer your question


I managed this situation severals times.
The best thing is to not modify the subscrition jobs created by SSRS service. After all they do not have a job schedule. The schedules are managed internally in the SSRS database.
With SQL Server Always on AG the jobs are not replicated because their definition is inside the msdb.
So in an Alwayson AG setup for SSRS, you have an SSRS database inside an AG but you have two (or more) msdb that can contain ssrs jobs and two (or more) SSRS services.
Each of the runnig SSRS services will execute the scheduled jobs handling dividing the workload trough the ssrs database. Here I'm supposing you configured the SSRS to use the AG listener for db connection.
Remember to place the proper ssrs service user in msdb database role (RSExecRole) of each instances as described here:
https://docs.microsoft.com/it-it/sql/reporting-services/security/create-the-rsexecrole?view=sql-server-ver15
The SSRS subscription jobs are recreated every time ssrs starts so when a failover occurs you have to start or restart ssrs service to handle this.
Then what happend depends on what is the role of AG you designed: HA or DR.
In the DR design I suggest you to not leave SSRS service turned off because it will execute subscription on a DR site and this usualy is not good.
In an HA design you could leave the SSRS service turned on so you balance the workload between the two instances (keep attention to licensing too)


Possible RMAN orphan files in backup directory

Maybe those files are not cataloged by RMAN? I would CATALOG them and then DELETE OBSOLETE once again.


I have managed to recover my data. (so happy)

After hours of reading forums and solutions I found a way to fix it. Here's what I did:

In xampp I backed up my old copy (after crash) of folder DbScrapes (dbname) from C:\xampp\mysql\data with files db.opt, scraped_user.frm, scraped_user.ibd
In C:\xampp\mysql\data I backed up an old copy (after crash) of files ib_logfile0, ib_logfile1, ibdata1.
Uninstalled my xampp
Restarted my computer to be sure ports that xampp is using will be close
Checked if there is a MySQL service in services.msc
On my computer there was a MySQL service.
I deleted it using cmd command sc delete "service_name"
Restart again to make sure everything is fresh.
Installed a new copy of xampp
Test if apache and MySQL is ok, if it's ok click stop
Copy the files that I have backed up to the location where I get them so it's C:\xampp\mysql\data
Run xampp's apache and MySQL again.

Now I'm running xampp and I have my data.

Note: Don't delete ibdata1 - it will delete all your database tables.

The crashing issue I think was a bug in xampp.


Remove characters from PostgreSQL database causing encoding errors

I think you're reading it backwards. Instead try to SET the client_encoding to utf8;.

SET CLIENT_ENCODING TO 'utf8';


Efficiently remove 90% of a table's data

I have three different ideas that I mentioned in a comment above. Here is a little elaboration on at least one of them (which you are stuck on due to self-diagnosed tunnel vision).

  Well, you could calculate the number of rows that make up 10% beforehand, and then compare that in your batch.

I was thinking about this but we don't really need to do the compare - we can just figure out what datetime value is at the edge of what we want to keep, and delete chunks of older rows until there are none left. For example:

SET NOCOUNT ON;

DECLARE
  @rc INT = 1,
  @cutoff DATETIME,
  @batchsize INT = 10000;

;WITH x(dt) AS
(
 SELECT TOP (10) PERCENT datetime_column
  FROM dbo.mytable
  ORDER BY datetime_column DESC
)
SELECT TOP (1) @cutoff = dt -- earliest row we want to keep
 FROM x
 ORDER BY dt;

WHILE @rc > 0
BEGIN
  DELETE TOP (@batchsize) dbo.mytable
    WHERE datetime_column < @cutoff
  SET @rc = @@ROWCOUNT;
END

You can see this post for some additional ways to enhance this.

  You could consider drop / select into instead of truncate / insert, and also think about using bulk-logged recovery during this operation.

I think this would be a valid option and should cause less pain than the above.

  You could also just perform one data movement operation:

SELECT TOP 10 PERCENT cols
  INTO dbo.newtable
  FROM dbo.oldtable
  ORDER BY datetime_column;

DROP TABLE dbo.oldtable;

EXEC sys.sp_rename N'dbo.newtable', N'dbo.oldtable', N'OBJECT';

This one is also less painful, but feel free to inject some debugging in there to be sure that you got the data you wanted before you perform the drop.

Note that in both of these latter cases there may be constraints, schema-bound views, etc. that prevent you from just dropping the table (obviously an inbound foreign key is not an issue because you are able to truncate, but outbound may still need to be dealt with). There will also be ramifications to existing plans (well deleting 90% of the data should also cause stats to update and invalidate plans, so really no different), and as @Kenneth points out below, you will need to re-establish permissions on the new table (so you might want to be sure you can script those in advance).


unknown opcode 2011

You are trying to use an older version of mongosniff to inspect traffic for a MongoDB server that uses a newer version of the MongoDB wire protocol.

Specifically, it looks like you are using a version of mongosniff older than 3.2 to inspect traffic from MongoDB 3.2 (which added the 2011 opcode for dbCommandReply).

To fix this you should use a version of mongosniff that matches your MongoDB server deployment.


Oracle sql combine several rows of the same table into one row

I think this will give you desired resultset :

select id,grp,itdesc,
MAX(CASE WHEN su=1 THEN DEN END) AS DEN,
MAX(CASE WHEN su=1 THEN NUM END) AS NUM,
MAX(CASE WHEN su=1 THEN SU END) AS SU ,  -- or just  "1 AS SU"
MAX(CASE WHEN cs=1 THEN NUM END) AS CS,
MAX(CASE
   WHEN sw=1 THEN NUM
   WHEN sw=0 THEN 0
   END) AS SW
FROM tab
GROUP BY
id,grp,itdesc


Can I use postgres_fdw without foreign tables defined?

postgres_fdw is definitely the better way. As you have read-only access to the production database, you can setup a FDW on your test (secondary) database provided you have superuser access to that (secondary) server. Or at least someone who can install the extension and setup the foreign server. Once those two things are there, you can proceed with a regular user.

One thing to keep in mind: if you do use the FDW, any select on the foreign table (in your test server) will generate some load on the production server. Whether or not that is acceptable only you can know.

  my SELECT is joining many tables from PRODdb1

If you join multiple foreign tables in a single statement those joins will be pushed down to the foreign server and evaluated there (at least with the Postgres 10 and I think 9.6 as well) only the result of the join will be sent back to the calling server.

Another option is to use pg_dump to copy the data from your production server to your test server.

pg_dump --host=prodserver --dbname=prod_db --table=public.some_table --file=some_table.sql --no-owner

That will export the data for some_table into a SQL script, including the necessary CREATE TABLE statement.

You can then run that SQL script through psql to import the data into your test server:

psql --host=testserver --dbname=test_db --file=test_table.sql

If you already have the table definition in your test server, then you might want to use --data-only for pg_dump to skip that part.

That will be faster than trying to generate some CSV files.


Repeating pattern X amount of times in LIKE

Yes. Function REPLICATE():

SELECT * FROM table WHERE account LIKE REPLICATE('[0-9]', 5) ;


Yes. When you "try" to authenticate directly to any replica set at the cluster, "local" admin database is used (admin database at the replica set).

You need to create that admin user locally. IF there are NO users inserted to your RS's admin database, "localhost exception" is still valid and you should not have any problem login without authentication (no -u and -p parameters on mongo -command) and create that admin user. After first user is created to RS, localhost exception is gone and you need authenticate. So, first created user MUST be admin with root rights.


How to return a null from an empty XML tag in SQL server using Xpath only?

Specify the text() node within the Value element. That node is missing when you have an empty tag.

Try this:

declare @X xml;
set @X = '<Value/>';

select @X.value('(Value/text())[1]', 'smalldatetime'),
       @X.value('(Value/text())[1]', 'varchar(max)'),
       @X.value('(Value/text())[1]', 'int'),
       @X.value('(Value)[1]', 'smalldatetime'),
       @X.value('(Value)[1]', 'varchar(max)'),
       @X.value('(Value)[1]', 'int');

Result:

------------------- ---------- ----------- ----------------------- ---------- -----------
NULL                NULL       NULL        1900-01-01 00:00:00                0

It is a good thing in general to always specify the text() node. The query plan is simpler and more efficient.


Oracle 11g - DROP database command on ACFS

And ACFS is not raw device, so the files will be deleted.

My ACFS:

[oracle@o6ca1 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@o6ca1 ~]$ acfsutil info fs /oradata
/oradata
    ACFS Version: 11.2.0.4.0
    flags:        MountPoint,Available
    mount time:   Wed Oct 31 15:21:46 2018
    volumes:      1
    total size:   10737418240
    total free:   8938848256
    primary volume: /dev/asm/oradata-379
        label:
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          247, 194049
        size:                  10737418240
        free:                  8938848256
        ADVM diskgroup         DATA
        ADVM resize increment: 33554432
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
        compatible.advm:       11.2.0.0.0
    number of snapshots:  0
    snapshot space usage: 0
    replication status: DISABLED

My database:

[oracle@o6ca1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? ORCL
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@o6ca1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 31 15:27:32 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/oradata/ORCL/users01.dbf
/oradata/ORCL/undotbs01.dbf
/oradata/ORCL/sysaux01.dbf
/oradata/ORCL/system01.dbf

Dropping the database:

SQL> startup force mount exclusive restrict
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             335545224 bytes
Database Buffers          725614592 bytes
Redo Buffers                5517312 bytes
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> exit

Files are deleted:

[oracle@o6ca1 ~]$ ls -l /oradata/ORCL/
total 0

And the free space is also greater than it was before:

[oracle@o6ca1 ~]$ acfsutil info fs /oradata
/oradata
    ACFS Version: 11.2.0.4.0
    flags:        MountPoint,Available
    mount time:   Wed Oct 31 15:21:46 2018
    volumes:      1
    total size:   10737418240
    total free:   10579529728
    primary volume: /dev/asm/oradata-379
        label:
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          247, 194049
        size:                  10737418240
        free:                  10579529728
        ADVM diskgroup         DATA
        ADVM resize increment: 33554432
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
        compatible.advm:       11.2.0.0.0
    number of snapshots:  0
    snapshot space usage: 0
    replication status: DISABLED


How to use comma separated columns in CASE WHEN Statement?

If you want to keep the CASE WHEN statements then you have to specify a new CASE WHEN statement per column / value in the IN() statement.

Example:

    WHERE supplierAccountDetail.SupplierMasterID
    IN(
    CASE
    WHEN @SupplierMasterID IS NOT NULL
    THEN @SupplierMasterID
    ELSE sales.ParentSupplierMasterID END
    ,
    CASE
    WHEN @SupplierMasterID IS NOT NULL
    THEN @SupplierMasterID
    ELSE sales.DivisionSupplierMasterID END);

This does mean that you will compare your column to the variable twice.

Alternatively you could change the query to what Akina mentioned in the comments:

WHERE (supplierAccountDetail.SupplierMasterID  = @SupplierMasterID AND  @SupplierMasterID IS NOT NULL)
OR
(
@SupplierMasterID IS NULL
AND
(
 supplierAccountDetail.SupplierMasterID IN(sales.ParentSupplierMasterID,sales.DivisionSupplierMasterID) )
);


Remove "almost" duplicate rows (may or may not start with plus sign)

WITH cte AS(
SELECT rowid, name,
CASE WHEN surname NOT LIKE '+%'
THEN '+' + surname
ELSE surname
END AS surname
FROM your_table)

DELETE FROM your_table
WHERE rowid NOT IN
(SELECT MIN(rowid)
 FROM cte
 GROUP BY name, surname
)


How to create trigger with NOT FOR REPLICATION

You have two options

OPTION #1 : Make the Column Immutable

DELIMITER //
CREATE TRIGGER update_table
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
    IF (NEW.updated_replication < OLD.updated_replication) THEN
        NEW.updated_replication = OLD.updated_replication;
    ELSE
        SET NEW.updated_replication = NOW();
    END IF;
END //
DELIMITER ;

OPTION #2 : Break the Trigger Midstream

DELIMITER //
CREATE TRIGGER update_table
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
    IF (NEW.updated_replication < OLD.updated_replication) THEN
        SELECT dummy FROM information_schema.tables WHERE table_name='whatever';
    ELSE
        SET NEW.updated_replication = NOW();
    END IF;
END //
DELIMITER ;

The line

SELECT dummy FROM information_schema.tables WHERE table_name='whatever';

should break the trigger since there is no column called dummy in information_schema.tables

This is a technique I have written about before

Apr 25, 2011 : Trigger in MySQL to prevent insertion
Dec 23, 2011 : check constraint does not work?

GIVE IT A TRY !!!


SQL Server 2008 - Restore Database With RESTART

Has this ever worked?

RESTART is only specified when you want to continue an interrupted restore.
If you can't change the SQL, then you can only somehow break a first restore to generate an error condition.

Because of this, you can't be sure that it's the same on SQL 2005 unless it happens every time. Or they changed behaviour.


MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine

OBSERVATION #1

I noticed you turned off autocommit. That will pile up so much data in ibdata1. Why?

There are seven(7) classes of information that is stored in ibdata1:

Data Pages for InnoDB Tables
Index Pages for InnoDB Tables
Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

Some of this info is made visible to certain transactions depending on the isolation level. Such actions could produce unintended primary key locks and lots of phantom data. As these two things increase, you should expect a fair slow down.

Recommendation: Leave autocommit on

OBSERVATION #2

I see you have this:

alter table item_load disable keys;

DISABLE KEYS does not work with InnoDB. Here is why:

MyISAM : DISABLE KEYS simply shuts off Secondary Index updating for the MyISAM table. When you mass INSERT into a MyISAM table with keys disabled results in a fast table load along with a building of the PRIMARY KEY and all unique indexes. When you run ENABLE KEYS, all Secondary Indexes are built linearly on the table and appended to the .MYD.
InnoDB : As shown in the internals picture of InnoDB, the system tablespave ibdata1 has  a structure dedicated to Secondary Index Insertions. At present, there is no provision to handle indexes the same as MyISAM.

To illustrate this, note my attempt to run DISABLE KEYS on an InnoDB table in MySQL

mysql> show create table webform\G
*************************** 1. row ***************************
       Table: webform
Create Table: CREATE TABLE `webform` (
  `nid` int(10) unsigned NOT NULL,
  `confirmation` text NOT NULL,
  `confirmation_format` tinyint(4) NOT NULL DEFAULT '0',
  `redirect_url` varchar(255) DEFAULT '<confirmation>',
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  `teaser` tinyint(4) NOT NULL DEFAULT '0',
  `allow_draft` tinyint(4) NOT NULL DEFAULT '0',
  `submit_notice` tinyint(4) NOT NULL DEFAULT '1',
  `submit_text` varchar(255) DEFAULT NULL,
  `submit_limit` tinyint(4) NOT NULL DEFAULT '-1',
  `submit_interval` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table webform disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'webform' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.00 sec)

mysql>

OBSERVATION #3

You noticed that MyISAM loads 20x faster than InnoDB. Would you like that to be more like 24-25 times faster? Then run the following:

ALTER TABLE item_load ROW_FORMAT=Fixed;

This will speed up INSERTs times 20-25% without any other DDL Changes. Side effect: The MyISAM table can grow 80%-100% in size, possibly larger.

You could run this on an InnoDB table as well, but the ACID-compliant behavior and MVCC of InnoDB would still be the bottleneck of its performance, especially if VARCHAR fields increase significantly are are written to ibdata1.


How to set the language of a doc to a DB field

I think I found a work around for you.

Set up a new dataset to pull back the language value (let's say language_dataset into column language_value).
Create a parameter, let's say called language.  Set it to internal and set its default value -> Get values from a query and use the dataset and value you just set up.
In the Report properties, Language -> expression and set the expression to =Parameters!language.Value.

Let me know if you need more details.


Does parameter sniffing exist for views?

The performance issue that you're seeing is not caused by parameter sniffing. The cause is that your query does not qualify for the parameter embedding optimization without a RECOMPILE hint. There's a lot of confusion about this online, but one could say that it's the exact opposite of parameter sniffing. The query optimizer doesn't know anything about the local variable so your query plan won't change based on the value of the local variable.

If you're still not convinced, try flushing the plan cache or restarting the server in a development environment. You'll still experience the same performance issue even though there aren't any cached plans available.

It is possible to experience parameter sniffing with a view if you execute the query using sp_executesql. There is an example of that in the earlier linked article.


SQL Server configuration / specification recommendations and advice!

What I'd do

4 disks for RAID 10 for all log files
Disk RAID 5 for all data files + OS
Don't bother with RAM disks

Why?:

With 24GB RAM all databases will be in memory so RAID 5 doesn't matter because disk reads won't really happen
Write performance is determined by log file throughput
Data files are larger then log files so RAID 5 is more efficient

What isn't ideal:

Not having one volume per database log file
Separation of local backup drives
Not having separate RAID 1 for OS + binaries

Other options:

Define "heavy write": you could go to RAID 1 for LDFs and another for OS for less than a few million rows per day


How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)?

How does SQL Server know which rows to delete?
To understand how it's processed, it's probably more helpful to look at the execution plan for the query. Setup scripts are at the end.
First, let's change the query a little bit to something that should throw an error, but doesn't.
DELETE A
FROM table1 AS A
WHERE EXISTS
(
    SELECT 1/0
    FROM table2 B
    WHERE B.id = A.id
);

If you were to just run SELECT 1/0 you'd get a divide by zero error. But there are places where expressions are present in the query for parsing, but aren't actually projected by the optimizer.
The "list of ones" you're referring to doesn't ever really appear. All the work to identify rows to delete is done via the where clause in the exists.
Reading the plan from right to left is the correct method when you need to understand the flow of data. Left to right is the flow of logic.

We have:

A full scan of both tables
A hash join to match rows
A delete from table1

The hash join figures out which rows need to go, and passes qualifying bookmark values on to the delete operator. In this case, bookmarks are used to uniquely identify rows because there's no clustered index available to use keys from.
With a unique clustered index, key values alone can be used to identify rows. With a non-unique clustered index, an additional unique-ifier will accompany any duplicate values, though this won't be visible to you.
Setup scripts
DROP TABLE IF EXISTS table1, table2;

CREATE TABLE dbo.table1 (id int NOT NULL);
CREATE TABLE dbo.table2 (id int NOT NULL);

INSERT
    dbo.table1 WITH(TABLOCK) (id)
SELECT
    x.*
FROM
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY 1/0) AS n
    FROM sys.messages AS m
) AS x;

INSERT
    dbo.table2 WITH(TABLOCK) (id)
SELECT
    t.*
FROM dbo.table1 AS t
WHERE id % 2 = 0;

SELECT TOP (100) t1.* FROM dbo.table1 AS t1;
SELECT TOP (100) t2.* FROM dbo.table2 AS t2;


If the Database is in Readonly mode how can we drop/remove those unused roles

If a database is in READ_ONLY mode, it is completely read-only. The read-only-ness is not limited to user data, but anything contained in the database. This means that both DML commands (ie, INSERT, UPDATE, DELETE), and DDL commands (ie, CREATE, ALTER, DROP) are prohibited.

If the database is a read-only replica of an Availability Group, or a read-only log-shipping secondary, then you would need to drop the roles on the primary replica, and the normal data movement will replicate those changes to your read-only secondary. It is not possible for the secondary to have different security roles than the primary.

If your database is a stand-alone database that has been put into READ_ONLY, then you would need to set the database to read-write before dropping the unused role(s), then set the database back to read-only:

USE master
ALTER DATABASE [<MyReadOnlyDatabase>] SET READ_WRITE;
GO
USE [<MyReadOnlyDatabase>];
DROP ROLE [<OldUnusedRole>];
GO
USE master;
ALTER DATABASE [<MyReadOnlyDatabase>] SET READ_ONLY;


Connect to mysql server with one url but have multiple masters

This isn't a replication problem to solve. Something like spider, max scale or vitess. Also, see the Percona toolkit for comparing tables.
Of course internally you could have some DNS based mechanism like p5.mydb.com to indicate these server has p5 and use this in the application's connection to the database.
If you have same databases in all slaves then you need a load balancer, which you can achieve with haproxy, ngnix. If you want to split read write between slave and master then you can look into proxySQL. If you have different databases then you need to manage that in the connection strings.


How do I create a user invitation model?

You could have a pretty simple schema for this with a "trick": treat all user -> list associations as "invitations", including the ownership relation.

You'd have one table for the users (with say a userid column).
Then a table for the lists (with a listid).
The relation between the users and lists tables could be:

userid | listid    | role
----------------------------
bob    | dbas      | owner
alice  | dbas      | invitee
alice  | sysadmins | owner
bob    | sysadmins | invitee
eve    | sysadmins | invitee

An invitation is simply another line in that table.
This allows you to have different levels of access to the lists. A user could "invite" another to have full-control of a list, just read access, or something in between - just define the appropriate set of roles for your use-case.

You might want to add things like "creator" information, last modification information to the lists themselves.


How to get exact year difference between two dates in SQL Server?

Subtract one from each other in YYYYMMDD format, and divide by 10000 using integer division (which rounds down).

So:

(YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate)
- YEAR(@DateOfBirth) * 10000 - MONTH(@DateOfBirth) * 100 - DAY(@DateOfBirth)
) / 10000

20150725-20000727 = 149998, and 149998 / 10000 = 14

You can also use CONVERT(int,CONVERT(char(8),@CurrentDate,112)), because style 112 is YYYYMMDD, but that's a little obfuscated in my opinion.


My strong advice (regardless of the RDBMS): store the files separately. This makes a much smaller database, important when you backup, migrate, replicate etc. Additionally, you can separate db and files on different disks, having so much more control over storage locations (e.g. disk subsystems with different fail-over strategies).

This means however, you need a way to serve the files to your clients, which requires extra work (but also extra opportunities, as you are not bound to the DB communication protocol).

A pro for storing in the db is obviously the simple way to return the data to the client.


Slow Performance of Parametrized Query in Excel due to Parameter Datatype

The issue appears to be parameter sniffing as the link you provided indicates.  I'm not sure how to fix the issue in Excel, but if you can figure out how to specify the datatype when it's creating the parameterized statement, that would fix it.

You have a few options that can help with the issue on the SQL Side.  I would only choose one of them and each of them have their price.  I think the best solution would be to use a stored procedure to mask it, but you will know your environment the best.

If you can modify the source table:

add a computed column of the NVARCHAR data type, equal to the RunID column, persisted.  Increased storage and a minor hit when RunID is inserted or updated but cleanest solution overall.  Modify your query from excel to use the computed column.
change the data type of the column to NVARCHAR.  This would probably impact everything in your environment, but would be the easiest to implement.
Create a stored procedure that accepts an NVARCHAR as an argument, then cast that to a different variable but with the correct data type, then return the desired results.  Clean solution, but requires a stored procedure be created and maintained.

To answer a question below (can't add a comment to my own answer, apparently).

You would create a stored procedure that accepts the NVARCHAR data type as the argument, then explicitly cast that value to a varchar type and use that in the query.  It saves the execution plan from doing a conversion on every row.  So something like this

CREATE PROCEDURE sptest (@inputparam NVARCHAR(50))
AS
DECLARE @queryparam VARCHAR(50) = CAST(@inputparam AS VARCHAR(50))
SELECT * FROM tabletest WHERE columnvalue = @queryparam


upgrading from postgres 9.1 to 9.3 on ubuntu server

There are basically three ways of upgrading PostgreSQL from different major versions (e.g. 9.1 to 9.3).

Upgrading with pg_dump

The first one, and recommended if possible, is to do a dump of the old (9.1) version using the binary of the newer (9.3) version and restore it on a new cluster created of the newer version.

This approach is, generally, the slower one, but also the most feasible. One tip to make it more fast, is using concurrency. To dump with parallel jobs, you can do:

$ pg_dump --format=directory --jobs=4 --no-synchronized-snapshots --file=/path/to/mydump mydatabase

You'll have to do it for each database you have, adjust --jobs=4 value to any value (test some values from 2 to number of cores, and see which gives better speed). Also, during this phase, nobody should be conected to the database, any modification will result in a corrupted dump (because of the non-secure option --no-synchronized-snapshots).

After that, you can restore you dump into the new instance using pg_restore:

$ createdb <options> -T template0 mydatabase
$ pg_restore --exit-on-error --jobs=4 --dbname=mydatabase /path/to/mydump

After that, it is recommended to run ANALYZE on your database:

$ vacuumdb --analyze-only mydatabase

(if you can afford the time, run only --analyze to also VACUUM the database and update the visibility maps)

Upgrading with pg_upgrade

Another option, is to use the contrib pg_upgrade. Using the --link method it provides a really fast way of upgrading PostgreSQL.

Before using you have to make a backup of the entire data directory, because in --link mode, if something goes wrong, you may loose both data (new and old). Also, read the entire docs and specially the notes at the bottom (there are some limitations for pg_upgrade).

UPDATE:
Please use the --check option before run the definitive command. Also, for large databases is recommendable to run this command in a screen session.

Upgrade using a trigger based replication tool

Another option to upgrade a version, is using a replication tool based on trigger. Like Slony, Bucardo and Londiste.

This is the option that requires the least downtime possible, but it is the hardest one to work on.

To do that you need to build a master-slave where the master is your current version (9.1) and the slave is the new version (9.3). You then, wait the first sync (with the system still in production), after that you close everyone connected to the database (the downtime starts here), wait for the slave to catch-up, promote it (the slave) to master and redirect all clients/applications to this new version. And you're done.

The Slony documentation provides a step-by-step to upgrade PostgreSQL using Slony.

Which one to choose

Well, as always depends, resuming:

The dump+restore is the most reliable, but generally the most slow one (the parallelism can give pretty good results though)
The pg_upgrade is one of the best options for little downtime (if you can use, see the limitations), it often takes only a few minutes, even for big databases
The trigger replication, is with no doubt the one that gives the least downtime possible (near zero), but it is really hard to achieve and I recommend only for experience people (on both PostgreSQL and the replication tool).

I hope I could help. Good luck.


The INSERT/UPDATE/DELETE permission was denied on the object 'TheTable', database 'TheDb', schema 'dbo'

Try with the below script. Its works for me.

GRANT SELECT ,UPDATE,DELETE,INSERT ON OBJECT::YourTable
    TO [Domain\User];
GO

I tried to the mentioned permission to a user Anoop,in the database MyLab.

GRANT SELECT ,UPDATE,DELETE,INSERT ON OBJECT::Account
    TO [*MyDomain*\anoop];
GO

And below is the screen shot after giving the permission.


Mysql Alter Tables took 5 hours to add 1 column

In my experience with MySQL Cluster, I found pt-online-schema-change as a very handy tool for appling live schema changes.

It works this way:

it creates a new real table B (with temporary purposes) as clone
of the table A that you want to change
then applies the schema change to table B. The operation is fast cause the table is empty and has no traffic on it.
it creates a trigger so that new records inserted in table A will be copied into table B
then slowly copies records from A to B in small chunks of data
them with atomic RENAME TABLE operations B becomes A

I'm not sure if it works with all kind of schema changes.

If the table is big, it will take a long time, but the table will remain available for read/write queries.

The mysql user that runs pt-online-schema-change must have particular privileges, but this is not clearly explained in the documentation (I've opened a bug ticket about this)


Ignore white spaces for comparison in unique constraints of Oracle

You can't do this with a unique constraint but you can do this with a unique index:

create unique index ix_name
   on the_table(regexp_replace(the_column, '\s+', ''));

If you want a case-insensitive index (where name 1 and NAME 1 are taken as the same value) you need to convert everything to lower or uppercase:

create unique index ix_name
   on the_table(upper(regexp_replace(the_column, '\s+', '')));


How to verify if given user@host with 'password' exists in MySQL 8.0+?

Actually, there is an alternative that MySQL (really Oracle) does not mention.

Back on Apr 22, 2015, I posted the Algorithm for the Password Function
MySQL 8.0 will still store the PASSWORD function output in the authentication_string column if you create the user using the mysql_native_password plugin.

Here is the algorithm

SET @plaintextpassword = 'whatever password you want';
SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@plaintextpassword)))));

GIVING CREDIT

I first learned this from a blog by a company called PalominoDB. They changed their name to blackbird. Pythian purchased blackbird. Pythian now has it as a blog post with this algorithm : Hashing Algorithm in MySQL PASSWORD()


You must repeat last operation and add * Quantita.

  SELECT A, B, C, (A + B) AS AB, (A + B) * C AS ABC

SELECT Capitolo.Descrizione,SottoCapitolo.Descrizione,
SottoCapitolo.Immagine,
SottoCapitolo.DescrizioneEstesa,
SottoCapitolo.Quantita,
ROUND(((SottoCapitolo.CostoUnitario)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeMargine)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeUtile)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeSpeseGenerali)),0)
 as CostoUnitario,
ROUND(((SottoCapitolo.CostoUnitario)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeMargine)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeUtile)+(((SottoCapitolo.CostoUnitario)/100)*PercentualeSpeseGenerali)),0) * SottoCapitolo.Quantita
FROM Preventivo inner join Capitolo on Preventivo.IdPreventivo = Capitolo.IdPreventivo inner join SottoCapitolo on SottoCapitolo.IdCapitolo = Capitolo.IdCapitolo order by Capitolo.Descrizione

You can avoid it by calculating this values in presentation layer.


Postgresql not using GIN trigram index when performing non-ASCII LIKE query?

I created your scenario with COLLATE = "C", and both queries use a bitmap index scan index on other_names_lower_trgm_gin as expected.

SQL Fiddle with a table of ~ 10k rows, Postgres 9.2.4, COLLATE = "C".

There is probably something wrong in your setup that is not in your question.
 Run (takes some time for big tables and an exclusive lock!):

VACUUM FULL ANALYZE city;

And try again ...


I don't know if you want to put it in laravel, but, here's the query:

SELECT
 t.ID,
 t.amount,
 s.Name as Supplier,
 sb.Name as Branch
FROM Transactions as t
JOIN Subsidiary as s ON (s.ID=t.supplier_id)
JOIN Subsidiary as sb ON (sb.ID=t.branch_id)
WHERE t.ID = 1;

Try it.

PD: You can change the JOIN syntax for  LEFT JOIN if you want to return the t.ID = 1 record from Transactions even if one column don't match in Subsidiary's JOINs.


How do I automate converting heaps into clustered indexes?

I would say that I agree with @MartinSmith.  Determining the clustered index key is something that requires some thought and planning.

But if you were looking to get a head start and generate some T-SQL code that, say, creates the CREATE INDEX commands on heaps that have an IDENTITY column (a generally accepted clustered index key), then you could do something like this:

declare @create_indexes nvarchar(max);
set @create_indexes = N'';

select
    @create_indexes = @create_indexes +
        'create clustered index ' + quotename('IX_' + object_name(i.object_id)) +
        char(13) + char(10) +
        'on ' + quotename(object_name(i.object_id)) + '(' + quotename(c.name) + ');' +
        char(13) + char(10) +
        'go' +
        char(13) + char(10)
from sys.indexes i
inner join sys.columns c
on i.object_id = c.object_id
where i.type = 0
and c.is_identity = 1;

print @create_indexes;

Again, I will restate that like @MartinSmith said, I wouldn't just blindly execute any DDL that will impact performance and design like this.  But the above is a start, and will give you the T-SQL for heaps that have an IDENTITY column.


Is there a way to avoid having chained/nested “replace()” items when using an ISO 8601 timezone offset value in a generated column?

After reading up on CONVERT_TZ, it seems like it would be the cleaner method of handling a case like this since I can cut down three (!!!) REPLACE statements with one like this:

CONVERT_TZ(`timestamp`,'+00:00',REPLACE(`offset`,'00',':00'));

It still seems a bit sloppy—since I am still dealing with having to convert offset values like -0400 to -04:00—but does work and is cleaner/easier to read and understand.

That said, the above method will fail for offset values such as +0530, +1000 and +0000. So instead of that, using the following method—which uses the INSERT() string function where colon (:) insertion is based on an offset value from the right calculated by the length of the string itself—is cleaner and works for a variety of different offsets:

CONVERT_TZ(`timestamp`,'+00:00',INSERT(`offset`,LENGTH(`offset`)-1,0,':'));

And these are the results using that with the following sample timestamps and related offsets:

SELECT CONVERT_TZ('2018-05-28 02:34:58','+00:00',INSERT('+0300',LENGTH('+0300')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 07:50:12','+00:00',INSERT('+0400',LENGTH('+0400')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 09:23:34','+00:00',INSERT('+0530',LENGTH('+0530')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 12:16:56','+00:00',INSERT('+1000',LENGTH('+1000')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 16:07:17','+00:00',INSERT('-0200',LENGTH('-0200')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 20:02:05','+00:00',INSERT('-0700',LENGTH('-0700')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 23:33:03','+00:00',INSERT('-1000',LENGTH('-1000')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 23:33:03','+00:00',INSERT('-0000',LENGTH('-0000')-1,0,':'));

And here are the results:

2018-05-28 02:34:58 with an offset of +0300 becomes: 2018-05-28 05:34:58
2018-05-28 07:50:12 with an offset of +0400 becomes: 2018-05-28 11:50:12
2018-05-28 09:23:34 with an offset of +0530 becomes: 2018-05-28 14:53:34
2018-05-28 12:16:56 with an offset of +1000 becomes: 2018-05-28 22:16:56
2018-05-28 16:07:17 with an offset of -0200 becomes: 2018-05-28 14:07:17
2018-05-28 20:02:05 with an offset of -0700 becomes: 2018-05-28 13:02:05
2018-05-28 23:33:03 with an offset of -1000 becomes: 2018-05-28 13:33:03
2018-05-28 23:33:03 with an offset of -0000 becomes: 2018-05-28 23:33:03


The proper key hierarchy is the Service Master Key protects the Database Master Key, the Database Master Key protects either an asymmetric key or certificate, the asymmetric key or certificate can be used for encryption or can be used to protect a symmetric key which is used for encryption. The keys are created in that order. The system creates and protects the SMK, and the DMK and subsequent keys are created by the user.

The design of the key hierarchy in SQL Server protects both the data and keys from compromise. Remember that with a symmetric key, the same key is used to encrypt and decrypt data, or in this case other keys. The main purpose of introducing an asymmetric key or certificate into the hierarchy protected by the Database Master Key is to prevent an attack on the DMK and SMK from inside of the database. If someone malicious wanted to obtain the DMK unencrypted, then that person would need to do one of two things. Either decrypt the service master key and use it to decrypt the DMK, which is not possible because only the database engine can use the Data Protection API to do this, or attempt to decrypt from the top of the hierarchy down, which would be possible if there were not an asymmetric key or certificate in the way.

All of the signed or encrypted bits of the symmetric keys in SQL Server are in a system table named sys.crypt_properties in each database, including the encryption of the Service Master Key in the master database. There is no system table that contains the private key for either of the asymmetric key types. If all keys in a hierarchy were symmetric keys, then the SMK would encrypt the DMK and the DMK would encrypt the symmetric key that would encrypt the data. Because of the way that symmetric keys work, that would also mean that, if someone opens the symmetric key for the data, then it can theoretically decrypt the DMK and the decrypted DMK can be saved by a malicious user or used to decrypt the SMK because the same key is used for encryption and decryption. This is why an asymmetric key or certificate is required to be an integrated part of the encryption hierarchy.


Procedure to find a specific EXPRESSION in all tables in a specific schema in ORACLE

I have a different approach to the same.

Adding the answer on top of the correct answer, just to reduce the manual work.

The following query will directly identify the number of rows in each table which is matching the criteria.

select table_name, column_name,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(1) as c '
      || 'from ' || table_name || ' WHERE ' || column_name || ' like ''%05510%'''))
  returning content)) as c
from user_tab_columns
WHERE DATA_TYPE IN ('VARCHAR2','NUMBER')  -- ALL DATATYPES YOU WANT TO SEARCH
ORDER BY 3 DESC;

Cheers!!


One address table for many types of addresses

Unless the attributes between the different types are totally different (which I doubt), go for the second option and store the address type in a column of the address table.


A SQL query is a definition of what data to return. There is a component of the database called the optimizer which determines how to get that data - which indexes to use, if any, in which sequence, which join algorithm to use etc. The output of that component is a query plan, the equivalent of a procedural program in the DBMS's internal execution language. The optimizer is free to choose any plan it likes as long as it is guaranteed to produce the output required by the SQL.
Sometimes queries which are logically equivalent but use different syntax will produce quite different query plans. Sometimes the optimizer recognizes the equivalence and produces the same plan for both statements. It can be difficult to predict which without good understanding of the working of the optimizer.
However, the optimizer does make available to us these plans. In PostgreSQL they can be retrieved using EXPLAIN. If the plans for the two queries are the same there will be no performance difference between them. Different plans will have different performance, though it may be slight.
There are a great many factors which go into constructing a plan other than the SQL. So a change in table cardinality (e.g. as time passes, or between production and development environments), or a different version of Postgres may produce a different plans for the same SQL statement.


How safe is it to widen a column in MySQL?

Here are the answers to your questions

  1.Are the ALTER TABLE commands above correct? (In particular I'm not sure if I should append a number-in-parenthesis to MEDIUMINT, and if so, what number it should be)

ALTER TABLE person_per MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0';
ALTER TABLE person_per MODIFY per_EditedBy MEDIUMINT unsigned default '0';

Yes, they are correct. You could also do this in a singe command:

ALTER TABLE person_per
    MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0',
    MODIFY per_EditedBy MEDIUMINT unsigned default '0'
;

The parentheses are not needed. In fact, the number will become 8 or 9.

  2.Will this alteration correctly preserve the data that is already stored in those two columns? I don't want to lose it.

The data will be preserved. In effect the alter table will behave like this:

CREATE TABLE person_per_new LIKE person_per;
ALTER TABLE person_per_new
    MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0',
    MODIFY per_EditedBy MEDIUMINT unsigned default '0'
;
ALTER TABLE person_per_new DISABLE KEYS;
INSERT INTO person_per_new SELECT * FROM person_per;
ALTER TABLE person_per_new ENABLE KEYS;
ALTER TABLE person_per RENAME person_per_old;
ALTER TABLE person_per_new RENAME person_per;
DROP TABLE person_per_old;

Under the hood, these are the steps that are executed. I trust that MySQL will perform your ALTER TABLE in like manner.

  3.Are there any obvious 'gotchas' that I might run into if I widen these columns? AFAICT SQL queries don't depend too much on integer columns being a particular width, but perhaps there is some non-obvious dependency that I should be aware of?

AFAICT you would only need to consider the size of the .MYD and .MYI files. Since SMALLINT is 2 bytes, and MEDIUMINT is 3 bytes, look for the files to increase byte 1 byte for each record. Thus, 1,000,000 rows in a 10MB MYD will now be about 11MB.

MyISAM tables with a wider row may need some adjustments (increases) in things like

key_buffer_size
join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
tmp_table_size

to support data pages, index pages, and temp tables that involve per_EnteredBy and per_EditedBy


Results for every hour of the day even data if not present

Take the query you already have and capture the output in a temp table #T.

select cast(T.creationDate as date) as ForDate,
       datepart(hour, T.creationDate) as OnHour,
       count(distinct T.userId) as Totals
into #T
from dbo.T
group by cast(T.creationDate as date),
         datepart(hour, T.creationDate)

Get the distinct ForDate values from #T and cross apply against a list of hours for one day and left outer join #T to get the Totals for each hour.

select dateadd(hour, H.OnHour, cast(D.ForDate as datetime)) as ForDate,
       isnull(T.Totals, 0) as Totals
from (select distinct T.ForDate from #T as T) as D
cross apply (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
                   (13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)) as H(OnHour)
  left outer join #T as T
    on H.OnHour = T.OnHour and
       D.ForDate = T.ForDate

drop table #T


How to search for a string in all columns of a table?

You can use an IN condition for that:

select *
from the_table
where 'Italy' IN (name, native, place);


Why is the processor % different in Activity Monitor vs Resource Monitor?

Resource monitor shows CPU from Processes and Services on two different graphs. (In your example, 32% + 37%, which means a total 69% CPU).

As for SQL Activity Monitor, it shows CPU utilization for the machine on which SQL is installed. (In your example, 64%, which is not that far from Resource monitor.)

I use a query to get the CPU utilization on my server. I run a job on my server and raise an alarm if I get an average over 90% for the last 10 minutes.

Here's my query:

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

-- Top 10 : for the last 10 minutes
SELECT TOP(10)  100 - SystemIdle as TotalCPU,
           DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
  SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
        AS [SystemIdle],
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
        'int')
        AS [SQLProcessUtilization], [timestamp]
  FROM (
        SELECT [timestamp], CONVERT(xml, record) AS [record]
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
        AND record LIKE '%<SystemHealth>%') AS x
  ) AS y
ORDER BY record_id DESC;


How to determine which one is my default instance in MS SQL Server?

login in to your server and run this

 SELECT SERVERPROPERTY('InstanceName')

for sql server 2008 and above if it returns null then it's a default instance else named one

  InstanceName: Name of the instance to which the user is connected.

  Returns NULL if the instance name is the default instance, if the
  input is not valid, or error.

  NULL = Input is not valid, an error, or not applicable.

  Base data type: nvarchar(128)

Ref

Another one using SEVERPROPERTY(),

Run the below code

SELECT SERVERPROPERTY('ServerName')

SELECT SERVERPROPERTY('MachineName')

if both returns same value ie machine then it's your default instance else
the first one return with your machinename\instance name

After your edit

if you want to check the SQL server service which listens to port 1433. Then you have to check that in SQL server configuration manager. Check-in SQL server network configuration in that protocols for SQL server and click properties for TCP/IP and see the port used under IP addresses


How to solve MySQL “The table is full” error 1114 with Amazon RDS?

Here is the Problem: Whenever you run ALTER TABLE, there must be a temp table used. The temp table is not being written to your data folder. It is most likely written to /rdsdbdata/tmp. Please this query

SHOW VARIABLES LIKE 'tmpdir';

This will give you an idea where that folder is. Again, by default, it is /rdsdbdata/tmp.

Why would that halt things for mysqld? Blame it on MyISAM. Why MyISAM? Here is something to consider: According to MySQL 5.0 Certification Study Guide,

bulletpoint #11 says the following on Pages 408,409 Section 29.2:

  If you run out of disk space while adding rows to a MyISAM table, no
  error occurs. The server suspends the operation until space becomes
  available, and then completes the operation.

When you run out of disk space, do not just shutdown or kill mysql. The count of the open file handles in any currently used MyISAM will not have been cleared. Thus, the MyISAM table is marked crashed. If you can free up disk space in the data volume with mysqld still running, mysqld will soldier on once disk space is made available. Please note that mysqld itself did not crash. It simply enters a state of suspended animation until free space materializes.

In your case, just kill the ALTER TABLE. The temp table, which uses MyISAM, simply locked up the disk space of /rdsdbdata/tmp. Killing the ALTER TABLE will get rid of the temp table and free up mysqld to continue with life as usual.

I have explained this in my past posts:

Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?
Apr 25, 2012 : Very Large Log Files

WHAT CAN YOU DO?

You may have to perform the ALTER TABLE yourself in manual stages.

For example, let's suppose you have this table:

CREATE TABLE ruchit_table
(
    id int not null auto_increment,
    field1 INT NOT NULL,
    field2 VARCHAR(20),
    primary key (id)
);

and you want to run ALTER TABLE ADD COLUMN field3 CHAR(5);

Here are the steps to perform this inside your datadir:

CREATE TABLE ruchit_table_new LIKE ruchit_table;
ALTER TABLE ruchit_table_new ADD COLUMN field3 CHAR(5);
INSERT INTO ruchit_table_new SELECT id,field1,field2 FROM ruchit_table;
ALTER TABLE ruchit_table RENAME ruchit_table_old;
ALTER TABLE ruchit_table_new RENAME ruchit_table;
DROP TABLE ruchit_table_old;

Note that

The temp table's location is in the datadir
Performed the ALTER TABLE

Added the new column to an empty temp table
Loaded the temp table
Got rid of the old table

These are things mysqld does internally with ALTER TABLE. These steps simply emulate this and does this emulation in the datadir.

Give it a Try !!!


Sounds like an issue with MySQL Workbench.

MySQL 5.5 has a default storage engine of InnoDB.

In fact, the option default_storage_engine is predefined as follows:

MySQL 5.5.4 and prior, MyISAM
MySQL 5.5.8 and after, InnoDB

There is another option called storage-engine, which was deprecated as for MySQL 5.5.3. Keep in mind that MySQL 5.5.8 was the very first GA release.

I would strongly suspect MySQL Workbench if it is coded to check storage-engine instead of default-storage-engine. This would allow MySQL Workbench to monitor older release of MySQL.


To anyone who cares,

Our SQL 2008 instance was not on SP3. After installing SP3 everything looks to have taken care of itself.


MySQL Enterprise Backup error: Log scan was only able to reach xxxx

I'm afraid your chance to do the incremental has passed.
You need to do two things:
Resize the InnoDB transaction logs
From the messages above, the logs look like 25600K.
You need much bigger logs because writes to the logs are going so fast that incremental backups will lose it place while backing up as you just experienced. You barely have about 512M with ib_logfile0 and ib_logfile1. The fact that an incremental fails shows you are writing more than 512M of transaction info during an incremental backup cycle.
In MySQL, the largest log allowed is 2047M.
Please do the following
STEP01) Run this in the mysql client:
SET GLOBAL innodb_fast_shutdown = 0;

This will cause all transactions to be completely committed to disk. This will shorten mysql startup time because InnoDB Crash Recovery is not needed (or at least drastically minimized).
STEP02) service mysql stop
STEP03) Edit my.cnf with this
[mysqld]
innodb_log_file_size = 2047M
innodb_fast_shutdown = 0

STEP04) Move the logs away
cd /var/lib/mysql
mv ib_logilfe0 ib_logilfe0.bak
mv ib_logilfe0 ib_logilfe0.bak

STEP05) service mysql start (Transaction Logs get recreated sized 2047M)
Please see How to safely change MySQL innodb variable 'innodb_log_file_size'?
Take a fresh Full Backup
Once you take this backup, you now have transaction logs big enough to endure a long-running incremental backup. It is still advisable to do increment backups during offpeak times.
Even with the transaction logs resized to 2047M, the only way an incremental backup's restoration could fail is if more than 4094M of transaction info is written to the log files during the incremental backup cycle.
UPDATE 2012-12-17 11:39 EDT
DISCLAIMER : Not Familiar with MySQL Enterprise Backup
I learned about 1.5 years ago that Percona XtraBackup was similar to MySQL Enteprise Backup. According to the Documentation on XtraBackup's full backup, you are supposed to prepare the Full Backup for restoration. This step is necessary because MySQL may have been writing transactions that are probably present in the double-write buffer that have not been accounted for in any other parts of the InnoDB infrastructure. That way, the following must be done:

transactions written during the backup are extracted from the double-write buffer
those transaction are executed
InnoDB files for the Backups are updated with proper Log Sequence Numbers

As for XtraBackup's Incremental Backup, the Full Backup must be prepared as well.
If the claim that Percona XtraBackup was similar to MySQL Enteprise Backup is indeed true, then there must be a set of preparatory steps that needs to be done once or twice to the Full Backup. Please read in the MySQL Enterprise Backup Documentation and find any reference possible to preparing Full Backups.
UPDATE 2012-12-17 12:55 EDT
After reading the MySQL Documentation on MySQL Enterprise Backup, I noticed something that may help. You stated that you do this:
mysqlbackup --backup-dir=/mnt/testb/full/latest --user=dba backup
mysqlbackup --backup-dir=backupDir/latest apply-log

I think you have a typo:
mysqlbackup --backup-dir=/mnt/testb/full/latest --user=dba backup
mysqlbackup --backup-dir=backupDir/latest --apply-log

I do not see apply-log in the Docs, but I do see these in the Docs

--apply-log
--backup-and-apply-log

Please try one of these options and see if it corrects matters

mysqlbackup --backup-dir=/mnt/testb/full/latest --user=dba backup-and-apply-log
mysqlbackup --backup-dir=backupDir/latest --apply-log


Storing Tlogs and data files on the same drive

It's a good idea to keep them separated, even if the drives are currently backed by the same storage device. It buys a lot of future flexibility, at a slight expense in management overhead for both the DBAs and the Windows/storage admins.

If you want to change the physical storage that backs either of the drives in the future, you won't have to go through all the databases and move the files at that point -- they'll already be in the right spot. Simply present different storage to the VM with the same drive letter, and assuming all the files and permissions are there, you're done. Simple.
If you collect Performance Monitor (PerfMon) physical disk stats, it's a good idea to record data file activity separately from log file activity. If that's even possible with all the files on the same drive, it probably won't be very easy.

Really I can't think of any significant downsides. There is the possibility that if you want to use a storage-level snapshot technology in the future, the SAN may not support having the files in two different physical locations. Consult your SAN admins or vendor on this; it may not be applicable now necessarily, but might come into play in the future. This isn't a negative, but it may play into the setup strategy as a constraint/requirement.

Aside from that, it's just a bit of extra management to keep things in the right spot, and you have to make sure that any automation scripts you have also respects the difference in location by the type of file. But the latter is something you should be doing anyway when writing scripts, so I don't see that as a negative.

It may be prudent to separate tempdb specifically into another location as well.


There are various ways to obtain multiple IDs with a single query in your situation.

First, you can just take the series of single SELECTs:

SELECT ID FROM FOO WHERE col1=1 AND col2=2 AND col3=3;

SELECT ID FROM FOO WHERE col1=4 AND col2=5 AND col3=6;

SELECT ID FROM FOO WHERE col1=7 AND col2=8 AND col3=9;

and combine them with UNION ALL:

SELECT ID FROM FOO WHERE col1=1 AND col2=2 AND col3=3
UNION ALL
SELECT ID FROM FOO WHERE col1=4 AND col2=5 AND col3=6
UNION ALL
SELECT ID FROM FOO WHERE col1=7 AND col2=8 AND col3=9;

Now you have a single statement returning all the matching IDs. It has to pass over the table as many times as you have tuples, but if the number is not big, that should not be a concern.

Another way is to use the OR operator to combine the conditions:

SELECT
  ID
FROM
  FOO
WHERE col1=1 AND col2=2 AND col3=3
   OR col1=4 AND col2=5 AND col3=6
   OR col1=7 AND col2=8 AND col3=9
;

You may also add brackets to make the intention absolutely unambiguous:

WHERE (col1=1 AND col2=2 AND col3=3)
   OR (col1=4 AND col2=5 AND col3=6)
   OR (col1=7 AND col2=8 AND col3=9)

though AND has higher precedence than OR, so the result would be the same either way.

While much simpler, the second method may not necessarily be more efficient. Internally, each member of the OR-ed expression may be implemented as a separate query, all of them being combined with UNION. So, under the covers the actual query executed could be very similar to the previous query, except UNION is not quite the same as UNION ALL, because it has the additional overhead of de-duplication of the results. If you expect your results to be unique anyway, de-duplication is certainly unnecessary.

In that case you might want to consider this method:

SELECT
  f.ID
FROM
  FOO AS f
  INNER JOIN
  (
    SELECT 1 AS col1, 2 AS col2, 3 AS col3
    UNION ALL
    SELECT 4, 5, 6
    UNION ALL
    SELECT 7, 8, 9
  ) AS p ON f.col1 = p.col1
        AND f.col2 = p.col2
        AND f.col3 = p.col3
;

The p derived table returns the argument values as a row set. That row set is joined against FOO and thus serves as a filter.

Although the last query looks more verbose even than the first one, it may turn out most efficient of the three methods.

It would have looked slightly more elegant, though, if SQLite had let you use the VALUES row constructor in the FROM clause:

SELECT
  f.ID
FROM
  FOO AS f
  INNER JOIN
  (
    VALUES
      (1, 2, 3),
      (4, 5, 6),
      (7, 8, 9)
  ) AS p (col1, col2, col3)
     ON f.col1 = p.col1
    AND f.col2 = p.col2
    AND f.col3 = p.col3
;

However, you can rewrite that using a CTE (Common Table Expression, also known as the WITH clause), like this:

WITH p (col1, col2, col3) AS
  (
    VALUES
      (1, 2, 3),
      (4, 5, 6),
      (7, 8, 9)
  )
SELECT
  f.ID
FROM
  FOO AS f
  INNER JOIN p ON f.col1 = p.col1
              AND f.col2 = p.col2
              AND f.col3 = p.col3
;

But, perhaps, the most elegant solution would have been this:

SELECT
  ID
FROM
  FOO
WHERE
  (col1,col2,col3) IN ((1,2,3), (4,5,6), (7,8,9))
;

if that syntax were supported in SQLite.

(Kudos to ypercubeᵀᴹ for the last two suggestions from the standard SQL.)

As of version 3.15, though, SQLite does support tuple comparison, as commented by a_horse_with_no_name, just not the specific syntax in the previous query. You can use the IN predicate with it only if you are matching against a subquery rather than an explicitly specified tuple list (thanks CL. for the suggestion):

SELECT
  ID
FROM
  FOO
WHERE
  (col1,col2,col3) IN (VALUES (1,2,3), (4,5,6), (7,8,9))
;

Or you can compare the tuples using the = predicate:

SELECT
  ID
FROM
  FOO
WHERE
  (col1,col2,col3) = (1,2,3)
  OR
  (col1,col2,col3) = (4,5,6)
  OR
  (col1,col2,col3) = (7,8,9)
;


Postgresql - how to securely delete the storage freed when a table is dropped?

drop table removes the table files from disk filesystem.

If you need them securely erased you could perhaps look for a filesystem that supports that.

On linux SSD filesystems, mounting with -O discard (or running fstrim after dropping) will hide the content of the deleted files from consumer-grade forensic tools (like photorec, dd, etc.) however nation-states with electron beam probes and the like may still be able to access the data if they can gain physical access to the media.

On magnetic disk running sfill will shred the deallocated blocks.


InnoDB, Hash Table, Reference Table and Fragmentation

Several ideas come to mind:

The hash table could be a memory table, once your inserts are done, you could then copy just the domains that don't already exist into the permanent domain_list table. Having your new data in memory might also make the domain name comparison between the hash table and the domain_list tables go faster.

Another option would be to use the innodb_file_per_table option and keep the database (idb) files on a faster disk.

A third option would be to load your entire dataset into a memory table and then convert that table to InnoDB using the ALTER TABLE table_name ENGINE=InnoDB. This will be much faster than inserting into InnoDB directly. This is possible since you control how and when data is inserted.


Finding the Table and Column Based on a Known Value

From my post about this issue here (see #1):

DECLARE
  @Search1 NVARCHAR(4000) = N'%a03dc6109c6f53ce93203f1b85c7d31d%',
  @Search2 NVARCHAR(4000) = N'%a03dc610-9c6f-53c-e932-03f1b85c7d31d%',
  @s NVARCHAR(MAX) = N'';

;WITH t AS (
    SELECT t.[object_id], [table] = t.name, [schema] = s.name
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
    WHERE EXISTS (
        SELECT 1 FROM sys.columns
          WHERE [object_id] = t.[object_id]
          AND (system_type_id IN (35,99) -- text, ntext
          OR (system_type_id IN (167,175,231,239) -- (n)(var)(char)
            AND (max_length = -1 OR max_length >=32) -- max or >= LEN(@Search1)
             )))
)
SELECT @s = @s + N'SELECT N'''
    + REPLACE([schema],'''','''''') + '.'
    + REPLACE([table], '''','''''') + ''',*
 FROM ' + QUOTENAME([schema]) + '.' + QUOTENAME([table]) + '
 WHERE ' + STUFF((SELECT '
 OR ' + QUOTENAME(name) + ' LIKE ' + CASE
      WHEN system_type_id IN (99,231,239)
      THEN 'N' ELSE '' END
      + '''' + @Search1 + '''' -- run again with @Search2
    FROM sys.columns
    WHERE [object_id] = t.[object_id]
    AND system_type_id IN (35,99,167,175,231,239)
    ORDER BY name
    FOR XML PATH(''), TYPE
).value(N'.[1]', N'nvarchar(max)'),1,6,'') + ';

'
FROM t;

PRINT @s;
-- EXEC sp_executesql @s;


How to simulate a situation when non-empty Transaction Log file will be added to the SQL Server Full Database backup file

There are two things going on here:
1: The database is effectively in simple recovery mode when doing your modifications, since you never did your first full backup after creating the database.
2: The column BackupSizeInBytes seems to be 0 for all ldf files, quite simply, even when there is log records in there. The documentation is vague for this column, so it difficult to say whether it is by design or not.


Can Oracle Named User Licenses be reused?

In general, you can move named user licenses around so long as at any given point in time you can identify the 20 humans (or non-human licensed entities) that have access to the system.  In your case, so long as there is never a point where all 40 users have access to the system, it sounds like you would be OK.

Of course, as with any licensing question, you would really need to talk with Oracle Sales and/or the folks that manage your licenses to get a definitive answer.  Unlike technical suggestions, licensing suggestions can't really be tried out and verified in your environment before implementation.  And if you get audited and it is determined that there is some issue with how you've managed your licenses, explaining that some guy on the internet told you it was OK is a career-limiting maneuver.


Transaction Log Shipping doesn't use the latest transaction log

Converting my comment to answer:

can change the restore frequency to be more frequent on secondary server, but then it wont keep up with the 15 min frequency of backups on primary server. Logshipping will pick up the logs (.trn and not the .wrk files) to restore


Avoid the problem by switching your table from MyISAM to InnoDB.


Equivalent SQL Server's Backup COPY ONLY in DB2

As far as I know you can't do that with DB2. Deltas and Incrementals will be based off of the last full backup taken (either offline or online). If anything it would cause one to adjust your recovery strategy for that week.

If you needed the data however, why not just copy the backups you need (the full and any deltas/incrementals) and restore those elsewhere, rather than taking a full backup? Or use db2look and db2move to extract the data for test purposes?

Otherwise I know IBM does have a product called IBM DB2 Advanced Recovery Feature (it was formerly an Optim product if I remember right). It has a subcomponent called  IBM DB2 Merge Backup for Linux, UNIX and Windows that allows you to combine the full backup with subsequent deltas and incrementals to create a full backup to restore to. This gives you the advantage of only needing to restore one backup, rather than progressing through the deltas and incrementals.


The purpose of your subquery as I understand it:
Select rows where the latest related entry in billing_pricequotestatus has a qualifying name.
Incorrect 2nd query

It's not immediately clear to me why my modification didn't produce equivalent output

The 1st query picks the latest row from billing_pricequotestatus and checks whether the name qualifies (name IN ('adjustment','payment','billable')).
The 2nd query is backwards: it check for any row with qualifying name (not just the last one). Also, it doesn't make sense to compute an aggregate in an EXISTS semi-join. You don't want that. And it's not equivalent.
Consequently, you get more rows from the 2nd query.
Incorrect time range
This predicate is a mess. Inefficient and possibly incorrect - or at least a ticking bomb:
WHERE pq.date_applied AT TIME ZONE 'PST'
       BETWEEN '2016-02-02T00:00:00'::timestamp
           AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
The column date_applied is of type timestamptz. The construct AT TIME ZONE 'PST' converts it to type timestamp and shifts by the time offset hard-coded into the time zone abbreviation 'PST' - which is a bad move to begin with. It makes the expression non-sargable. That's more expensive and, more importantly, rules out the use of any plain index on date_applied.
Even worse, the time zone abbreviation 'PST' is not aware of DST or any historic shifts in time. If your time zone has (or had in the past) daylight saving time, and your set spans different DST periods, your current expression is most probably incorrect:

"AT TIME ZONE" with zone name PostgreSQL bug?
Ignoring timezones altogether in Rails and PostgreSQL

You would need to use the applicable time zone name instead of the abbreviation to get consistent local time - which is even more expensive.
And there's yet another problem: While the column value is shifted by a hard-coded time offset ('PST'), your upper bound '2016-03-03T22:27:41.734102-08:00'::timestamptz is provided as timestamptz and silently coerced to match the data type timestamp. Since no explicit time offset is provided, the cast defaults to the time zone of the current session. So you can get different results depending on the current time zone setting of your session. I can't think of a use case where this would make sense.
Don't do any of this. Don't translate timestamptz column date_applied to local time at all, don't mix data types like you do and don't mix different ways to cast. Instead, use the column as is and provide timestamptz parameters.
Query
SELECT i.quote_id, i.acct_id AS account_id, sum(i.delta_amount) AS amt
FROM   billing_pricequote pq
JOIN   LATERAL (
   SELECT name
   FROM   billing_pricequotestatus
   WHERE  quote_id = pq.id
   ORDER  BY created_at DESC
   LIMIT  1
   ) pqs ON pqs.name IN ('adjustment', 'payment', 'billable')
JOIN   billing_lineitem i ON i.quote_id = pq.id
WHERE  pq.date_applied BETWEEN (timestamp '2016-02-02T00:00:00' AT TIME ZONE 'PST')  -- !
                           AND timestamptz '2016-03-03T22:27:41.734102-08:00'
GROUP  BY 1,2;

Note the LATERAL join, but not LEFT JOIN, make it an INNER JOIN to implement your predicate right away.
Or use the equivalent correlated subquery outlined by @ypercube. Not sure which is faster.
Also note that I base the LATERAL JOIN on billing_pricequote - before joining to the big table billing_lineitem. This way we can eliminate rows early, which should be cheaper.
Index
Currently, you get:

Seq Scan on billing_pricequote pq

Only 70k of your 1,5M rows are selected, which is around 5 %. An index on date_applied might help a bit, but not much. However, this multicolumn index should help substantially if you can get index-only scans out of it:
CREATE INDEX foo ON billing_pricequotestatus (quote_id, created_at DESC, name);

Even more efficient with name_id instead of name as suggested below.
Statistics
Postgres is over-estimating the selectivity of your time range:

(cost=0.00..88,546.50 rows=7,313 width=4) (actual time=2.353..767.408
rows=70,623 loops=1)

It might help to increase the statistics target for just the column date_applied. Details here:

Configuring PostgreSQL for read performance

Table definition
Exemplary for billing_pricequotestatus:
name seems to be one of a couple of possible types. It would help performance to normalize some more and just use a 4-byte integer referencing a lookup-table instead of a varchar(20) repeated over an over in 3.3M rows. Also, reordering columns (if possible) like I demonstrate would help some more:
   Column   |           Type           |              Modifiers
------------+--------------------------+------------------------------------------
 id         | integer                  | not null default nextval('...
 quote_id   | integer                  | not null
 created_at | timestamp with time zone | not null
 updated_at | timestamp with time zone | not null
 name_id    | integer                  | not null REFERENCES name_table(name_id)
 notes      | text                     | not null
See link above about alignment and padding. To measure row size:

Measure the size of a PostgreSQL table row

And "name" is not a good identifier. I would use something descriptive instead.


The SQL Server will maintain these indexes automatically.  If they have become fragmented you'll need to defragment them manually or via a scheduled job just like the user databases.


How to call from a shell program a postgresql COPY FROM FILE command, passing the path of the FILE as an argument?

Utility commands like COPY and many others do not accept expressions where literals are expected, so the file name must be given verbatim.
You may use \set in psql to build the filename by concatenating a path with a name, as in:
$ cat script.sql
\set path :v1 '/new_nomenclature.csv'
COPY nomenclature FROM :'path' CSV ....

$ psql -f script.sql -v v1="/tmp"

When \set is not suitable, another way is to call SELECT to build the string, get the result into a psql variable, and feed it back to COPY (this might come handy if building the path requires more than a mere concatenation)
$ cat script.sql
select :'v1' || '/new_nomenclature.csv' AS path \gset
COPY nomenclature FROM :'path' CSV ....


Audit MariaDB Which User Account Privileges Are Being Used

The audit plugin, when enabled, will provide the access information associated with users accounts.

There are also authentication plugins to centrally manage authentication.


Equivalent of PGAdmin III Server Status Activity Window in PGAdmin 4 v5

I came up with this query that returns the same columns as PGAdmin III Status Activity window but alas there is no color-coding of queries running longer than 5-10 seconds and no button that will terminate the query or the connection.
select pid as process_id,
application_name,
datname as Database,
usename as username,
client_addr as client_address,
backend_start as client_start,
query_start,
xact_start,
state,
state_change,
pg_blocking_pids(pid) AS blocked_by,
query
from pg_stat_activity
where state is not null
order by state,xact_start desc;
This query is based loosely on this post and this post


ora-01410 invalid RowID sporadically in a select statement

Are there other processes operating on the same tables? Indexes being rebuilt?

If so, then you could be hitting the situation that Jonathan Lewis describes here: http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/


Using SQL Snapshot Replication to refresh TEST databases from PRODUCTION. Why not?

Your scenario is interesting :

Small databases approx 1GB in size.
Number of databases = 20
Refresh of the data occurs on nightly basis, so the data can be stale by one day.

Initial thought reveals that snapshot replication should be OK as you thought, but I would highly recommend to go for backup/restore method - safe, reliable and less overhead of maintaining.

Note that I am not saying that snapshot replication is a bad choice, but why not use a proven and reliable method - backup restore ? You can/should automate it (backup/restore) using either Powershell or T-SQL or SSIS.

Drawbacks of Snapshot replication :

You cannot drop/create tables, sp's, functions, that are marked for replication. Also, you cannot directly alter a table, you have to use system stored procedures sp_repldropcolumn and sp_repladdcolumn.
A standard snapshot will lock the tables during the BCP step to make sure you get a valid copy.
Any schema changes requires a new snapshot to be generated which is quiet resource intensive on the publisher side.
Additional overhead on the server occurs if you have a local distributor.
End up monitoring multiple services running on the server.
Dependency on SQL Server Agent.
Windows NT permissions on the snapshot folder.
Network bandwidth.


Would it be correct to add extra columns to the junction table according to normalization rules?

yes you can add columns to a bridge table, as long as they are attributes to that for that relationship.
Customers     CustomerProducts      Products

customerId    customerId            productId
              productId
              price
              quantity
              buyDate

The columns price, quantity and buy_date, are all specific to the that entry


Locking/transactions in MySQL Stored Procedure

Do these with autocommit=1, with PRIMARY KEY(request_hash):

DELETE FROM openduty_dedup
    WHERE triggered < NOW() - INTERVAL 10 SECOND
      AND request_hash = $hash;
INSERT IGNORE INTO openduty_dedup
    (request_hash, triggered)
    VALUES ($new_hash, NOW());

If the INSERT fails, either there is an entry in the last 10 seconds, or someone else snuck in just now.  If you need to know about success, check Rows_affected(), which is connection-specific and does not need to be in a transaction.

No need for START TRANSACTION.  No need to pre-compute "now".  No need for GET_LOCK().  The only flaw is the hard-coded 10 second window.


Not authorized on admin to execute command mongoDB: Atlas M0 Free Tier cluster

Since you are using a hosted database service (MongoDB Atlas), many administrative commands are limited as they are either provided via the management interface or likely to introduce performance/stability issues.

The Atlas free tier has additional limitations since it is backed by a shared MongoDB instance. See: Unsupported Commands in M0/M2/M5 Clusters.

  "not authorized on admin to execute command {setFeatureCompatibilityVersion: \"3.4\" }"

The setFeatureCompatibilityVersion command is an administrative command used during upgrades. You cannot set this on Atlas Free Tier (M0) or shared (M2/M5) clusters.

  db.createUser(...)

User permissions and roles for MongoDB Atlas must be managed via the Atlas user interface or API. See: Add MongoDB Users.