unable to bring database online from recovery pending I am trying to bring a SQL Server database online and accessible  that is in Recovery Pending state.
The SQL Server service uses an Active directory account that is a domain administrator and a member of sysadmin on sql server. The SQL Server account should have rights in the SQL file folders as it is a domain admin. It is however not a local administrator in the SQL Server.

The error messages are:

  Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file
  "F:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBName.ndf". Operating system
  error 5: "5(Access is denied.)". Msg 5120, Level 16, State 9, Line 1
  Unable to open the physical file "F:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBName.ndf". Operating system
  error 5: "5(Access is denied.)".

While

alter database [DBName] set online;

results in the error below:

  Msg 945, Level 14, State 2, Line 1 Database 'databs' cannot be opened
  due to inaccessible files or insufficient memory or disk space.  See
  the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line
  1 ALTER DATABASE statement failed.

Can you think of a solution?

The first issue was someone denied the SQL Server Services in the NDF and MDF files and once that denied was removed and granted appropriate access, the first issue was resolved.
The second solution was that corrupt NDF file had no data so I removed it and database was back online.


Normalizing team leader Each user is on exactly one team. A team has exactly one user in the team_lead role. How can we normalize this in the database? Examples in pseudo-SQL:

Strategy 1:

table user:
id       int  pk
team_id  int  fk references team(id)
is_lead  int  //1 or 0

table team
id            int  pk

Problem: Multiple users on one team could be marked as the team lead

Strategy 2:

table user:
id       int  pk
team_id  int  fk references team(id)

table team
id            int  pk
team_lead_id  int  fk references user(id)

Problem: There is no guarantee the referenced team lead is actually a member of that team

Is there any way to normalize this in the database design, or do we have to use constraints or enforce on the front-end?

There's a (standard SQL) way of getting the guarantees you want, but I'm afraid mySQL 5.7 is not able to handle it. My example has been tested on PostgreSQL.

You can define your users table this way:

-- Schema for everything
CREATE SCHEMA teams ;

-- Users
CREATE TABLE teams.users
(
  user_id integer NOT NULL PRIMARY KEY,
  team_id integer,
  CONSTRAINT users_team_id_user_id_key UNIQUE (user_id, team_id)
);

Then, have your teams have be defined in the following fashion:

-- Teams have one team_lead
CREATE TABLE teams.teams
(
  team_id integer NOT NULL PRIMARY KEY,
  team_lead_id integer NOT NULL,

  -- This constraint ensures that 'team_lead_id' is a 'user_id', and
  -- this user is actually a member of the team
  CONSTRAINT teams_team_lead_id_fkey FOREIGN KEY (team_lead_id, team_id)
      REFERENCES teams.users (user_id, team_id) MATCH FULL
      ON UPDATE RESTRICT ON DELETE RESTRICT
      DEFERRABLE INITIALLY DEFERRED
);

By using a composite foreign key constraint you actually make sure that the user you are checking for, is actually a member of the team.

After this second table has been defined, you can now add the foreign key constraint users.team_id -> teams.team_id:

-- The 'team_id' on users is a
ALTER TABLE teams.users
  ADD CONSTRAINT users_team_id_fkey FOREIGN KEY (team_id)
      REFERENCES teams.teams (team_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
      DEFERRABLE INITIALLY DEFERRED ;

Note that both tables are referrrig to each other. You have circular references.

Being this the case: the constraints must be checked at the end of a transaction, not just at the end of a statement. In the case of PostgreSQL, they need to be defined DEFERRABLE and INITIALLY DEFERRED (this is what mySQL cannot handle). You need to enter data for the first team (table teams) and its team leader (table users) all in one transaction, or, otherwise, there's no way to satisfy both FOREIGN KEY constraints:

Let's assume these are 'user 1' and 'team 5':

BEGIN TRANSACTION ;
INSERT INTO teams.users (user_id, team_id)      VALUES (1, 5) ;
INSERT INTO teams.teams (team_id, team_lead_id) VALUES (5, 1) ;
COMMIT TRANSACTION ;

Unfortunately, mySQL 5.7 documentation about foreign key constraints states:

  [...] InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things are impossible, such as deleting a record that refers to itself using a foreign key.

MariaDB documentation about FKs doesn't have mentions about deferrability.


Is there a database table design to avoid storing table names in a table? So my scenario demands to store details of businesses which offer varied services. One business can offer n different services. Since each service is different, details pertaining to a service are in individual tables.

table : business
------------------
business_id,
(other business specific fields)

table: service_types
----------------------
service_type_id,
service_name,
(other service specific fields)

table: business_services
--------------------------
service_type_id_fk,
business_id_fk,
identifier (id to point the record in respective service table)
(other business service specific fields)

table: service_a (similarly many other services - service_b, service_c etc)
------------------
service_id,
service_type_id_fk,
(other service specific fields)

Now I need to find out

How many services a business provides
Details of each of the service.

For 1, I can simply look up business_services table.

But for 2, with the design I have, I have to store the tables names somewhere; either in server side scripting or in a dedicated table. I can may be store it in service_types table where I store the service name and its table name for further details.

Is it good practice to store table names in a table?
Or what could be the best way to address the problem?

NOTE:
None of these services are similar and they demand a table for each one. So grouping services into one table is not an option because I want it to be normalized and distribute load as well.

If you know in advance that you're going to have at most a smallish (under 10) set of services and you don't need to add/remove/change them programmatically or often, I would use separate link tables for the services. Since the client needs to request a service to be added, it's probably a lot simpler to create a new link table and make code changes to reflect the new service than to create a "clever solution" which is overly clever for the amount of changes you're going to have.

With generic solutions you tend to exchange efficiency/simplicity and other things for ease of use (e.g. you have just a single generic link table, but you end up with more complexity because you can't have a foreign key, your queries need to work with your custom solution, you must differentiate the services somehow in your application anyway, etc.).

Even if you were to have a lot of services and they would often change, I wouldn't go for the "dynamic link table" approach. You can definitely make it work somehow, but it would be a hack rather than a good solution. So what would be a good solution in that case? Hard to say from the question alone, but I'm sure it would involve a lot more than just the database.

The gist of the question is "How can I treat different things as if they were the same, except in many cases they need special treatment", and I'm offering the "don't even try if it isn't strictly needed" answer. If that's not to your liking, you'll have to explain a lot more about your system, the services, the data, etc.

One (not saying the best) way to get the amount of services provided by a business would be to use UNION for example. It gives a readable (and easily editable!) query, for when a service is added.

SELECT COUNT(*) FROM (
SELECT 1 FROM business bus JOIN business_services_a a ON (bus.id = a.business_id)
UNION
SELECT 1 FROM business bus JOIN business_services_b b ON (bus.id = b.business_id)
UNION
SELECT 1 FROM business bus JOIN business_services_c c ON (bus.id = b.business_id)
)


Keeping messages in a backup table after dequeue is completed I would like to transfer the enqueued messages to a backup table after they have been dequeued.

Currently we are using DBMS_AQ.DEQUEUE with mode dequeue_mode := DBMS_AQ.REMOVE.

So after the message gets dequeued, the  queue table does not keep any entry.
But we need the message in a backup table once dequeued, so that we can check the backup table for debugging purposes.

If I use browse mode, would it help ? Are there any other alternatives?

I am using Oracle 11g.

I see two options:

1) use DBMS_AQADM.ALTER_QUEUE to change the retention of the queue. This way, when you REMOVE a message, it still stays in the queue table with a status of PROCESSED. You can specify the retention period. The table remains fast even with these messages there. When you are done, you can either manually delete the rows or let the database clean it up when the retention period has expired.

2) I'm not sure I would recommend this, but I've seen people put a ROW-DELETE trigger on the queue table for this purpose.


Replication recommendation I am asked to investigate viability to use SQL Server replication technology for one of our upcoming project. In this project, basically there will be one central server located at corporate HQ (say Hub) and there will be multiple servers located at remote locations (say Nodes). All nodes will get data by using our application and Hub will get data directly injected into SQL Server. I am given 2 business requirements that need to be satisfied by this solution,

Data to be synchronized between hub and nodes both ways.
Data from one node NOT to appear on another node.

So based on those requirements, I chose to try Merge Replication.For test, I setup hub and 2 nodes. Hub is also acting as distributor. Then selected all objects of database and created subscription. And registered both nodes as subscribers. And this setup works... almost. The issue I am facing is, if I update data from node1 it appears in hub as well as node2 (and vise versa). That doesn't satisfy our 2nd requirement.

I am familiar with Replication but not master at it and I would really appreciate if someone can suggest if something is wrong with my approach. Or is merge replication not a good choice for my case?

I am using SQL Server 2014 standard editions for all 3 instances installed in Windows Server 2012R2.

Not an ideal answer....but may be we will have to add a table column and use that to denote different locations. So, with merge replication in place, the node1 location bit as 0 and node2 location bit as 1....so it doesn't matter for hub.....however, for node1 can have further reporting proc code with that location filter. Same way for node2 process code also. And don't replicate this proc. Seems some coding effort. So, basically we are letting the data to replicate..... however limiting it to not come in reports by using filtered proc code. Thanks.


Is a process created in Oracle for every SQL statement? After reading the Oracle docs, it´s still not clear for me if an Oracle process is created for every SQL statement or just for every connection (or not really related to both).

Any comment here would be really great.

Thanks.

A process is created per connection, which can then execute SQL statements for the duration of the connection.

A SQL statement may use more than one Oracle background process (for things like Parallel Query) during its execution, but these aren't forked specifically for the SQL statement.

Note that I'm ignoring things like connection pooling (DRCP) and shared servers (MTS) to keep the answer simple.


How to log SSAS $system.discover_sessions to a SQL Server Database We are running SQL Server 2014 and SSAS on the same box.  Every now and then our SSAS service appears to freeze.  When it does I check the msmdsrv.log file and it tells me that spid x is blocking transaction on spid y.

Is it possible to log the results from SSAS $System.discover_sessions to a database on the SQL Server on a regular interval?  I'd like to capture the current session so that when these issues come up I can see the commands that are running and the SSAS instance refuses to allow me a connection when this occurs.

You could log this periodically by scheduling an SSIS package with a data source to the SSAS server and a destination to the SQL Server but I think your better option would be to have a look at the Flight Recorder. (There unfortunately isn't an enormous amount of official documentation.)

Flight recorder basically captures a sort of server side trace continuously which you can then analyze (and even replay) in profiler to see what was happening at that point in time.

Alternatively you could set up a profiler/server side trace yourself or use the ssasdiag tool.


Why does @@dbts increase after backup/restore? I have a SQL Server 2005 database, containing some tables which have a Timestamp (or RowVersion) column.
I need to know the value of the current timestamp, so I use the following query: SELECT CAST(@@dbts AS BIGINT);

This will return, for example, 10505.

Immediately afterwards, without updating, inserting, ... anything, I do a BACKUP DATABASE and a RESTORE DATABASE and I run the SELECT query again.
Only this time, the result is 14000, while none of the timestamps in the tables have increased.

Why/how does this happen?

The value for dbi_maxDbTimestamp is stored on the database boot page. (page 9 in the primary data file).

This is not written to every time a timestamp value is allocated. Instead SQL Server reserves a few thousand at a time.

For example if @@DBTS is 2000 and the dbi_maxDbTimestamp is also 2000 then SQL Server updates the value written in the boot page to 6000 the next time it needs a timestamp value.

The values from 2001 - 5999 are allocated in memory and "lost" if the database is set offline and then online again.

The backup contains the copy of the boot page that has been updated to 6000. So upon restoring it the timestamp values will start from this number. It knows nothing about any lost intermediate values.

To see this

CREATE DATABASE DbtsTest

GO

USE DbtsTest

GO

DBCC TRACEON(3604);

CREATE TABLE T (X ROWVERSION)

SELECT CAST(@@dbts AS BIGINT);

DBCC PAGE(DbtsTest,1,9,1)

On my system for a newly created database @@dbts is 2,000. The DBCC PAGE output from above is

I have highlighted the timestamp value. CAST(CAST(REVERSE(0xD007000000000000) AS BINARY(8)) AS BIGINT) = 2000

INSERT INTO T DEFAULT VALUES

SELECT CAST(@@dbts AS BIGINT);
DBCC PAGE(DbtsTest,1,9,1)

Now the @@dbts is reported as 2001 but looking at the page itself.

the timestamp value has changed. CAST(CAST(REVERSE(0x7017000000000000) AS BINARY(8)) AS BIGINT) = 6000.

Running

DBCC DBTABLE('DbtsTest')

at this point to view the DBTABLE structure shows both values

dbt_maxDbTimestamp = 6000
dbt_dbTimestamp = 2001

Finally

BACKUP DATABASE [DbtsTest] TO
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\DbtsTest.bak'
WITH NOFORMAT,
     NOINIT,
     NAME = N'DbtsTest-Full Database Backup',
     SKIP,
     NOREWIND,
     NOUNLOAD,
     STATS = 10

Then looking at the backup shows it is the 6,000 figure that is written.

Restoring the database and querying SELECT CAST(@@DBTS AS BIGINT) returns 6,000 as expected.


pg_restore: [custom archiver] could not read from input file: end of file Not going into details how that happened, all started with a corrupt DB. It was OK on the file-system level, but trying to:

select count(*) from mytable

resulted in the following:

  ERROR:  could not read block 257798 in file "pg_tblspc/16386/PG_9.6_201608131/16385/16506.1": read only 0 of 8192 bytes

The table is very large (~200GB) and contains some bytea columns with binary data. It is never updated, only inserted into and selected from.
I run pg_dump on it, it was working for a long time, produced a ~200GB file, but failed in the end with a similar error:

pg_dump -Z9 -Fc -d mydatabase -t mytable -v -f /datadir/mytable.backup

  pg_dump: Dumping the contents of table "mytable" failed: PQgetResult() failed.

  pg_dump: Error message from server: ERROR:  could not read block 257798 in file "pg_tblspc/16386/PG_9.6_201608131/16385/16506.1": read only 0 of 8192 bytes

  pg_dump: The command was: COPY public.mytable (id, account_id, fetched, col1, col2, col3, col4, col5) TO stdout;

Then I tried to restore it, again it was working a long time, in the end it took ~200GB space on that tablespace, however select count(*) from mytable returns 0.

 pg_restore -Fc -d mydatabase -v /datadir/mytable.backup

  pg_restore: processing data for table "public.mytable"

and after a long time

  pg_restore: could not read from input file: end of file

On server side this resulted in:

  ERROR:  canceling statement due to user request

  CONTEXT:  COPY mytable, line 14497030

  STATEMENT:  COPY mytable (id, account_id, fetched, col1, col2, col3, col4, col5) FROM stdin

  LOG:  could not send data to client: Connection reset by peer

  FATAL:  connection to client lost

It looks like the pg_restore requested to abort the whole operation.

I would like to recover as many rows as possible, I suspect that all the data should be still there since it's basically immutable, and that just the last record is corrupted.

Is there a way to force Postgres to keep the records which were restored up till the failed one?

I would suggest you try to do something along the line of:

SELECT count(*) FROM mytable WHERE my_primary_key < value ;

... and find at which point it fails. [This may be tedious, but you can just cut in half repeatedly, until you find the value that first fails.]

You will most probably need to alter the different settings that affect index usage, because you actually want to force the database to use the index, even if it actually must scan 99% of the table. You want it not to scan the page that booms.

If you can get SELECT to give you most of the data, you can then do something such as:

CREATE TABLE my_table_2 AS
SELECT * FROM my_table WHERE my_primary_key < value;

and later on:

ALTER TABLE my_table RENAME TO my_table_old ;
ALTER TABLE my_table_2 RENAME TO my_table ;

... and you'll have all the data that could be retrieved. I wouldn't drop the old table, in case someone finds later on a better method of retrieving the missing info.

Best of luck.


Having Sharepoint on a different Collation than the system Databases? After installing Sharepoint I realized that the collation of all Sharepoint databases is Latin1_General_CI_AS_KS_WS while my server collation is Latin1_General_CI_AS. From official documentation I see that all CI collations are supported. Anyway, would it be helpful for me to rebuild the system databases into the same collation to avoid conversion problems in temp db?

Quote

  We support any CI collation for the SQL instance (for master, tempdb
  databases). However we recommend using Latin1_General_CI_AS_KS_WS as
  the instance default collation (master, tempdb databases).

I don't find the best practice here, please help me.

Having a different default collation for the instance than for the SharePoint DB is not necessarily a problem. The main thing to ensure is that the SharePoint DB is set to the correct collation for SharePoint, which is Latin1_General_CI_AS_KS_WS, and which you have done.

You quoted a section from Supportability regarding SQL collation for SharePoint Databases and TempDB (in the future, when you quote something, you really need to attribute the source) which clearly states that SharePoint does indeed "support any CI collation for the SQL instance (for master, tempdb databases)". Hence, Latin1_General_CI_AS being case-insensitive, is supported.

The only other guidance I have found, and which makes sense, even outside of SharePoint, is this:

SharePoint SQL Collation - Best practice

The main point expressed there is that if you have more running on this instance of SQL Server than SharePoint, then sure, use whatever collation makes sense. Else, it is easier to just have it match between SharePoint and the system DBs.


Raise application error in a stored procedure when inputs contain keywords like 'delete' , 'insert','update' I have a Stored Procedure which gets 6 input queries and the inputs are the same as where clause of a query. For instance where x=1 and z=5 and e=4. The Application makes these input strings and pass it onto my procedure.This is a part of my procedure in which I'm checking the inputs in order to validate the content of each input query so that they do not contain keywords like "select", "update", "delete" or equivalents.I want to know whether there are better ways to do that.
create or replace procedure app_error_test(query1 nvarchar2,
                                           query2 nvarchar2,
                                           query3 nvarchar2,
                                           query4 nvarchar2,
                                           query5 nvarchar2,
                                           query6 nvarchar2) is
queryconcat nvarchar2(30000);
begin

--**************** validate content of input queries

queryconcat := lower(nvl(query1, '') || nvl(query2, '') ||
                     nvl(query3, '') || nvl(query4, '') ||
                     nvl(query5, '') || nvl(query6, ''));

 if (queryconcat like '%drop%' or
      queryconcat like '%delete%' or
       queryconcat like '%execute%' or
        queryconcat like '%truncate%' or
         queryconcat like '%create%'  or
          queryconcat like '%update%'  or
           queryconcat like '%insert%') then

     RAISE_APPLICATION_ERROR(-20032, 'ILLEGAL CONTENT');
  end if;
end;

Thanks in advance

I would use regex_like:
select column_value
     , case
           when regexp_like(' '||column_value||' ','\s(drop|execute|truncate|create|insert|update|delete|merge)\s', 'i')
               then 'Yes'
       end as illegal
from   table(ora_mining_varchar2_nt
             ('drop table customers', 'just dropping by', 'undroppable'));

COLUMN_VALUE              ILLEGAL
------------------------- -------
drop table customers      Yes
just dropping by
undroppable

I've added merge to the list as I assume you are trying to prevent SQL injection. If so, there are still risks even if you filter for keywords. execute isn't a SQL keyword, though. Did you mean call?
Your procedure would become:
create or replace procedure app_error_test
    ( query1 nvarchar2
    , query2 nvarchar2
    , query3 nvarchar2
    , query4 nvarchar2
    , query5 nvarchar2
    , query6 nvarchar2 )
as
    queryconcat nvarchar2(30000) :=
        query1||' '||query2||' '||query3||' '||query4||' '||query5||' '||query6;
begin
    if regexp_like(' ' || queryconcat || ' ', '\s(drop|call|truncate|create|insert|update|delete|merge)\s', 'i')
    then
        raise_application_error(-20032, 'Illegal content in supplied SQL: '||queryconcat);
    end if;
end;


Select Count on Big table take 15 minutes I try to run select Count on table with like 16M rows, with MySql 5.5 and workbench 6.2.
The query running almost 1000sec . Is it normal?
The query :

SELECT COUNT(1) FROM My_Table

when I run explain I see that mysql using the pk index.
Don't have other indexes or Unique values in the table.
I know I can use the estimation in ALL_TABLES , but I need accurate number.
Do you have any ideas ?

The DB locate on AMAZON AWS - RDS , and I using the defualt parameter group ,
There are any changes you recommended to do?

Thanks

Yes, it could be normal. With a "large table", you are likely needing to do a lot of I/O (unless you're using InnoDB and you've got a huge buffer pool allocated.)

Unless you're paying for Amazon Provisioned IOPS, your I/O rate is going to be somewhat constrained on shared volumes.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html

So, two basic approaches to addressing the issue: 1) increase the rate at which you can perform I/O from storage (i.e. pay for Provisioned IOPS), and 2) re-organize your data so that requests don't require as much I/O. And that's going to depend somewhat on which storage engine you're using (MyISAM, InnoDB, or something else), but the design goal would be the same, get a much smaller index which MySQL can scan, rather than scanning the entire table. Use EXPLAIN to determine the execution plan MySQL is using, whether it's going to use your index.

Also, you might consider implementing a more advanced approach, for example, if there is a large portion of the table that is "static" history, where the count of those rows won't change. Get the count of those rows separately, and store that result.

For example, all rows that have a date column before a given value... get that count and store it in a small table. This would only have to be done once:

 CREATE TABLE my_counts (before_dt DATE, mycount BIGINT);
 INSERT INTO my_counts (before_dt, mycount)
 SELECT '2014-11-01' AS before_dt, COUNT(1) AS mycount
  FROM mytable t
 WHERE t.dt < '2014-11-01';

Then, to get an "up-to-date" count, you can reference that stored value, and then you'd only need to get a count of rows that aren't included in that count. An index on the dt column would be appropriate. Combine the result from:

SELECT COUNT(1)
  FROM my_table t
 WHERE t.dt >= '2014-11-01'

With the result from:

SELECT mycount
  FROM my_counts c
 WHERE c.before_dt = '2014-11-01'

That is, you can live with a "stale", cached count of a portion of the table.


Addition With Null Values I have inherited a table that has NULL values for float fields, instead of a 0 value.  I am doing some basic testing for querying, and in my tests both ISNULL() and COALESCE() present the same accurate output.  In my testing (a table with roughly 5K records) time difference is very negligible as well.

Question being with the sample DDL below, is there a benefit to use ISNULL() over COALESCE() or vice-versa?  Are there circumstances that I have yet to see that could provide an inaccurate calculation?

Create Table #Test
(
  blue float
  ,red float
  ,green float
)

Insert Into #Test VALUES
('14', NULL, '12')
,(NULL, '12', '10')
,(NULL, NULL, '8')
,('10', '2', NULL)

Select
ISNULL(blue,0)+ISNULL(red,0)+ISNULL(green,0)
FROM #Test

SELECT
COALESCE(blue,0)+COALESCE(red,0)+COALESCE(green,0)
FROM #Test

COALESCE can take a whole list of values to check for NULL where ISNULL takes one check value and would require nesting to check more values.

SELECT
COALESCE(col1, col2, col3, col4, col5, 0) AS value_returned
FROM myTable

vs

SELECT
ISNULL(col1, ISNULL(col2, ISNULL(col3, ISNULL(col4, 0)))) AS value_returned
FROM myTable

Since your case is using float values, there should not be a difference in results from calculations.  The only benefit I can really see if code readability and minimizing the amount of nesting needed.


Design of fact table(s) and dimensions tables for data warehouse Hi i'm newbie in Datawarehousing, how would you model this in a Data Warehouse:
i wish design the Data Warehouse which  give the answers of statistics relating to a baseball league
For players
in offensive:

•How many times has a batter to bat.
•How many runs scored is.
•How many hits,doubles hit and triples hits.
•How many homeruns did.
•many  RBI.
•many base on balls

in Defensive:

▪ How many outs, double play takes
▪ How many assists has
▪ How many errors lead or Pitcher:
▪ How many games has lost
▪ has won many games
▪ How many saved games
▪ How many complete games leads
▪ How many games have started many
times it has bleaching
▪ How many hit, double hit received, received triple hit, received homerun

It is important to check the equipment-specific information, this information is:

• How many games won as a visitor and
as homeclub
• Games lost as a visitor
• Games lost as a homeclub
• How many times lost/gained when
rival starting pitcher was left-handed
• How many times lost/gained when
rival starting pitcher was
right-handed
• How many games won / lost in extra
inning

Additionally it is important to check the information of each of the games, this
information is:

• Which team was homeclub
• Which team was visiting
• On what date was played
• In what city was played
• What was the score (it is a string of the form AB where A is the amount
of runs scored by the winning team and losing team B)
• The winning team
• What pitcher lost
• What pitcher won
• What pitcher saved
• Number season game

Other important highlights

• Must be able to view detailed
information for each player (name,
Arm, etc).
• Must be able to view detailed information for each team (Name, Headquarters, etc).
• A season has 3 stages or Heat, or  RoundRobin or Final Round
• It is important to know at-bat hitting a player, this shift comes from the alignment (first base, second  base, etc)
• It is important to know the alignment of each play in defensive

As for the data of the players, it is important that this can be viewed as
season, as each inning game even as regards data equipos.En equipment is important that this can be viewed as
season.
Does anyone know some references on that precise part of DW design ? Any ideas?
Thanks a lot.

The IBM Redbooks have a couple of good titles on modeling a data warehouse.

Data Modeling Techniques for Data
Warehousing
Dimensional Modeling: In a Business
Intelligence Environment

You might be better served with an OLTP database in 5NF, though. It's not hard to test simplified versions of both.


What are the side-effects of disabling nested loops in postgres? A query of multiple joins spends a lot of time on nested loops. I disabled the nested loops and the query ran much faster. However, I have been told that nested loops might give me wrong results by skipping some tuples. Is this true?

Using debugging parameters like enable_nestloop should not change the results of a query. If it does that'd generally be a bug.

However, you should not use enable_nestloop = off or similar in production. It's a very big hammer, and while it might appear to fix your immediate problem, it'll create performance problems elsewhere. A nested loop is often the correct plan node for best performance in a part of a query, and forcing PostgreSQL to prefer other options might make queries significantly slower.

Instead, you should generally adjust the cost parameters like random_page_cost to encourge PostgreSQL to choose a plan that performs better on your hardware and workload. For more information see the tuning advice on the PostgreSQl wiki and in the main documentation.


Drop multiple databases in one script I am trying to figure out how to drop multiple databases on PostgreSQL in one sql script. I preferably need it to work both when executed in pgAdmin console, and when used in psql command line tool. When I do:

DROP DATABASE db1;
DROP DATABASE db2;

I get following error:

  DROP DATABASE cannot be executed from a function or multi-command string

Is there any way around this problem? I.e., is there any way to run each command separately (like when using "GO" statement on MSSQL), or some other sensible way to easily drop multiple databases? I need this functionality for maintenance scripts during development.

When executing multiple commands in a script in pgAdmin they are automatically wrapped into a transaction. You can explicitly begin and commit transactions, but not with commands that won't run in a transaction context. The manual:

  DROP DATABASE cannot be executed inside a transaction block.

Similar in psql when called with -c command. The manual:

  If the command string contains multiple SQL commands, they are
  processed in a single transaction, unless there are explicit
  BEGIN/COMMIT commands included in the string to divide it into
  multiple transactions.

However, when fed to psql via standard input:

  This is different from the behavior when the same string is read from
  a file or fed to psql's standard input, because then psql sends each
  SQL command separately.

  Because of this behavior, putting more than one SQL command in a
  single -c string often has unexpected results. It's better to use
  repeated -c commands or feed multiple commands to psql's standard
  input, either using echo as illustrated above, or via a shell
  here-document, for example:

psql <<EOF
\x
SELECT * FROM foo;
EOF

So you can use psql with standard input in default autocommit-on mode.
Else, you can only run separate commands.

You could use the shell-command dropdb - or write a shell-script with it that drops multiple databases.

BTW, the only difference between what you can run from pgAdmin and psql are the meta-commands of psql - which are not SQL. Those are interpreted by psql and not the database engine. To switch between meta-commands and SQL, use the separator meta-command \\. There is an example in the manual.


How to improve performance merging multiple table via cursor I have the following stored procedure to merged multiple tables into 1 table. The records that im tryin to merge is about 150M. The procedure is quite slow. Here is the query
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE Category CHAR(200);
DECLARE cur1 CURSOR FOR SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='technology2' AND TABLE_NAME <>'technologyRepo';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur1;
  read_loop: LOOP
  FETCH cur1 INTO Category;
     IF done THEN
       LEAVE read_loop;
     END IF;

  SET @table_name:=Category;
  SET @QRY=CONCAT('INSERT INTO
  technologyRepo (
                  documentId,
                  url,
                  version,
                  first_detected,
                  last_detected,
                  technology_name)
                  SELECT
                  id,
                  url,
                  version,
                  first_detected,
                  last_detected,', QUOTE(Category));
SET @QRYFRM=concat(@QRY, ' FROM technology2.');
SET @sql:=CONCAT(@QRYFRM,@table_name);
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
END LOOP;
CLOSE cur1;
END

Here is the total number of tables that i need to merge:
+------------+
| TABLE_NAME |
+------------+
|       1178 |
+------------+
1 row in set (0.16 sec)

mysql> SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA
= 'technology2' AND TABLE_NAME <>'technologyRepo';
+-----------------+
| SUM(TABLE_ROWS) |
+-----------------+
|       150116963 |
+-----------------+
1 row in set (0.02 sec)

Any performance optimization for this? I'm not that familiar with cursor.

Don't use a cursor, use a JOIN to do the entire set of statements all at once.
Here are two examples:
http://mysql.rjweb.org/doc.php/myisam2innodb#generating_alters
http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes
Both of them depend on doing lots of statements, then you manually copy the commands into MySQL commandline tool (or some other tool).  They could be adapted to do prepare/execute.
See also the following for construction a single query to pivot a table:
http://mysql.rjweb.org/doc.php/pivot


Table Design for user-specific and user-agnostic criteria I want to design a table that is general enough to accommodate an increasing number of achievements as I come up with new accomplishments to reward.  Initially, I thought to design the table as follows

Name (of Milestone)
Description (of Milestone)
Metric

So for the last milestone listed above, I might say

Name: 1k All-Star
Description: Completed a total of 1000 tasks
Metric: 1000

The code would check the number of completed tasks against the metric to determine whether to award the milestone.  But there are two types of milestone examples I listed.  One in which the metric is universal for all users, and one in which the metric is subjective and depends on the user's previous actions.

Would I want to define universal metrics on one table and make a separate milestone table for the user-specific achievements?  I imagine there that I might create a secondary table of these user-specific metrics so as not to repeat fields:

Subjective Milestone Table

Name
Description
Metric - one-to-many connection to Subjective Metric Table

Subjective Metric Table

Name
User
Metric

So as an example

Name: Weekend Warrior
Description: Exceeded the record for most number of tasks completed on a Sunday
Metric: 5
Name: Weekend Warrior
User: Samson
Metric: 5

Or am I going about this all wrong?

Track your achievements in parts.  Right now you have some indicative (i.e. descriptive) information and one "business rule" column (Metric).

You actually need more than one business rule column.  Depending on what you foresee being your needs and how much work you want to put into now, this might be either one or two more business rule columns, or possibly even an extra table or two.

In addition to "how many are needed?" (i.e. Metric) you also want a column that describes how many of what?  You have two descriptive columns for this now, but these are human readable. You also want a machine readable column.  In terms of nomenclature, I would actually use Metric for the what, and something else, say Scalar for the how many.

With a Metric column and a Scalar column you can then write program logic that knows how to measure each type of achievement.  This way you don't need custom logic for different levels.

You could take this a step further and normalize this somewhat so that the descriptive information is part of a separate table.  This would allow you to have groups of achievements that share certain types of information and logic.  This distinction would help you with handling user-specific and user generic logic in a nice, managed, organized way.


Tiered permissions to generate conditional data access I am working to improve the confidentiality and access level of the information presented within a program interface over top of a MySQL database. My goal is to create a two tier system: one based on access-level, and the other based on physical location.

For instance, an engineer will have greater complexity and more access than someone within HR. While at the same time users in China will be able to review information specific to them, and will not be able to see information for Australia. I thought about creating a permissions table, with all the possible combinations stored in it, and each unique combination would have a number associated with it, but I would like administrators to have the ability to add new permissions at will and in a manner that is fairly painless. I have also toyed with the idea of a single character for company and another for group-level, but this would give a limit of 62 companies (assuming for simplicity case-sensitive alphanumeric values) and 62 regions. Is there an obvious solution that I'm just not seeing?

It isn't too hard to create table-driven access rules if you can base the rules on something that is in the data already, like a country code.

You want to beware the power of multiplication.  Don't try to create access rules based on the cross-product of two or three or more factors.  The management of such a rule set will quickly become impractical due to the shear amount of data.

Instead, keep each access rule separate (i.e. country, department) and treat each as a gate that the user has to get through before they get access.  This will be a bit of work when setting up your data access code, but it is the best way to operate the system in the long term.


How to structure Master/Detail tables for performance without denormalizing? Some Background Info

We have a set of tables that holds all the transactions for our system, TransactionHeaders and TransactionDetails.  There are roughly 80k Transactions a day which translates to 80k rows in TransactionHeaders and 900k rows in TransactionDetails being burned daily.  At current, the TransactionHeaders has about 10 million rows and TransactionDetails has about 110 million rows.  We use the data for general reporting.

dbo.TransactionHeaders (
TransactionHeaderId            int                            identity,
TransactionHeaderTypeId        int                            not null,
StoreId                        int                            not null,
TransactionDate                date                           not null,
TransactionTime                time                           not null,
...)

dbo.TransactionDetails (
TransactionDetailId            int                            identity,
TransactionHeaderId            int                            not null,
ItemUPC                        NCHAR(14)                      not null,
Price                          NUMERIC(16,2)                  not null,
ReplicationDate                datetime                       not null
...)

The Issue

Querying has become cumbersome.  It takes a very long time to access the sales of a given store or a given item for any period.

What I've Tried

I have tried to bring TransactionDate down to the TransactionDetails table in order to partition it on the date with one partition per day.  This worked great for finding the sales of an item.  The problem is that many of the reports require the StoreID in addition to being over a specific date range.

Given that adding more information from the TransactionHeader table to the TransactionDetail table breaks the pattern, I'm hesitant to denormalize the tables into one table due to storage concerns.

I've had the idea to partition TransactionHeader on TransactionDate and partition TransactionDetail on TransactionHeaderID.  In theory this makes the queried data significantly smaller and reinforces the pattern by making the details only reasonably accessed via the header information.

The Question(TL;DR)

Is there a preferred, correct, standard, etc. method for dealing with tables in the Master/Detail pattern in order to increase performance?  Partitioning one or both tables? I'd like to avoid denormalizing if at all possible.

You've got a few different questions in here:

Q: It takes a very long time to access the sales of a given store or a given item for any period.

A: To troubleshoot that, we would need to see the execution plans of the queries involved, plus know a little about the query runtime and the hardware involved. 10mm rows in a header table and 110mm rows in a detail table isn't much at all for SQL Server, so this should be a solvable problem.

Q: (Partitioning) worked great for finding the sales of an item. The problem is that many of the reports require the StoreID in addition to being over a specific date range.

A: Correct, partitioning rarely makes SELECT queries faster. It's more about improving performance of bulk loads, specifically partition switching. I wouldn't think of partitioning as a solution to this problem, and indeed, it will actually make most queries worse.

Q: Is there a preferred, correct, standard, etc. method for dealing with tables in the Master/Detail pattern in order to increase performance?

A: Absolutely - archive older data. Figure out what you're going to let users query online at high speed, and then beyond that, move the data into a separate set of archive tables. You can use a partitioned view over the old and new tables in order to give them a single seamless view into the data for easier reporting too.

There's a lot of advantages to this approach. For example, when you want to add additional fields to the current table, you can do that quickly without having to deal with a large amount of archive data. If you want to add lots of indexes to the old archive data, you can - because it's not getting tons of inserts/updates/deletes anymore. If you split the old and new data into different databases, you can even use different backup/recovery strategies with them - even while the view is in place, and users don't know the data is split.


How to sum measure value for all records between two dimension values I have a cube, called metric_cube, with a measure of Metric and a dimension Week with the given output on SSAS from SQL Server 2014.

Current Output:

Week  Metric
----  ------
 1      6
 2      5
 3      4
 4      3
 5      2

I want to get the cube to output the sum of the Metric for all combinations of a from Week and to Week, where the from Week <= the to Week.

Desired Output:

Week(F)  Week(T)  Metric
-------  -------  ------
  1         1        6
  1         2        11
  1         3        15
  1         4        18
  1         5        20
  2         2        5
  2         3        9
  2         4        12
  2         5        14
  3         3        4
  3         4        7
  3         5        9
  4         4        3
  4         5        5
  5         5        2

I have tried using the same dimension twice, but I get a cross join error. I have also tried creating two dimensions and applying both of them, but all that does is duplicate the Metric value from the current output for all to Weeks without consideration of the desired constraint for the from Week <= the to Week

Is it possible to use a data cube to achieve the desired output? If so, how?

First you need two cube-dimensions because you cannot use one dimension on more than one axe of a MDX-select. You can use role-playing dimensions, that means there is only one "Week" dimension in the database but it is used twice with different names for the cube.

Second each Week dimension has to be connected to a measure group. If you use only one measure group with one column "WeekId" you can only get a measure not null when you select the same week from both dimensions.

One solution is to create a facts-table with the cartesian product of all combinations Week1Id, Week2Id and a value precalculated to the sum you wanted.

Or you use two measure groups, one for "FromWeek", one for "ToWeek" and store a rolling sum of the metric, here in one table:

Week  SumFrom   SumTo
1       0         6
2       6        11
3      11        15
4      15        18
5      18        20

and create a calculated

MEMBER [measures].[result] AS ([ToWeek].CurrentMember, [Measures].[SumTo]) - ([FromWeek].CurrentMember, [Measures].[SumFrom])


Oracle RAC 11g creation I would like to play with RAC configuration.

Using virtual area (VMware or VirtualBox):

RAC1 (Windows 2003 Server)
RAC2 (Windows 2003 Server)
Shared storage (Openfiler)
DNS server (for using SCAN feature)

In process of RAC config I will need to insert static IP address, subnet mask, gateway. Is it possible to create RAC configuration in virtual area, not touching work Active Directory network (I mean not adding IP to working office network)?

This is definitely possible both with VMware and VirtualBox and any other mainstream virtualization product as well I guess.

On my VMware vSphere sandbox, I have created an additional virtual switch that is not mapped to any physical network adapters. This network is not visible/reachable from the outside world, but VMs attached to it can reach each other through interfaces using this switch, so it's perfect for a RAC private network with 1 host machine.

In VirtualBox, I use NAT Network (available since version 4.3) for RAC private network. It's a similar concept, a virtual network not visible/reachable from the outside world, but VMs with interfaces in this network can communicate each other through it.

You can use the above for RAC public networks as well, so you don't need to allocate any IP addresses on your real physical network. For example, in VirtualBox, you can use 1 NAT network interface as "management interface", and define port forwarding rules on it, so you can access the VM from your local machine thorugh RDP/SSH without using the VM console. Then use another NAT Network interface for the RAC public network, and another NAT Network interface for the RAC private network, so 3 interfaces in total.

I would not bother with openfiler, both VMware and VirtualBox support shared virtual disks.


PostgreSQL FDW: is it necessary to specify sever and user for every link to a database on the same server (with identical user privileges)? I have a server with roughly 7000 databases that all have a practically identical schema. I've been querying them and collecting information about their contents in a separate database on the server. The way I have done this so far is using dblink queries wrapped in some Python code that iterates through all the databases. This is fine when I'm able to construct a query beforehand and iterate it, but it's not very efficient if I just want to quickly query one of the databases as dblink queries are somewhat complicated to construct.
I haven't used FDW in the past but I figured it would be much simpler to just import the relevant schema(ta) from every database on the server and store those as objects in my main database. What trips me up is that the FDW documentation begins by suggesting the creation of a foreign server object and user mapping. Since every database is on the same server and queried by the same user, I don't want to begin by adding a step that might be unnecessary.
So my question is, is it necessary to specify server and user mapping to create connections to databases on the same server, with a user that has identical permissions across every database?

Yes.  You can't use a foreign server without creating it.  And you would have to create one for each database you want to link to, as dbname can only be specified at the foreign server level, not at the level of individual foreign tables.  And each server needs its own user mapping, they can't be shared.


How to change the output encoding of Oracle command line tools to UTF8? I am using a german oracle 11.2.0g install, which has some command line tools communicating with some non-ascii characters (ü, ö, etc.). These are written out in iso8859-1 encoding, but I need them as utf-8 for some scripting. Is it possible?

Additional info:

OS: german windows 7 x64
Tools: command line tools in the bin/ directory of the oracle install, f.e. lsnrctl and like
Scripting language: mostly cmd.exe

Language-specific output is okay, it gives the german output without any problem. The only problem is that it comes in iso8859-1 and not in utf-8.

It were converted by an iconv command from a cygwin, but I used this only as a last resort. The best were if I were able to set somehow the utf-8.

Another tools (f.e. tools from cygwin) work without any problem.

Thank you very much - I found the solution.

First, the output encoding of the Oracle command line tools can be controlled by

either the NLS_LANG environment variable (in my situation, a value of GERMAN_GERMANY.AL32UTF8 was needed)
or through the registry by the key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\NLS_LANG

Thank you very much for all - I leave this question to make the googlers of the future happier. :-)


Mongodb remove reference of corrupt shard I have a sharded Mongo 3.0.14 setup

2 QueryRouter
1 Config server
3 Cluster (2 data node + 1 arbiter) each with 2TB

How to remove 3rd cluster without draining process? I am okay with the data loss on cluster 3

You need to go "under the hood". Shutdown everything else than config server (mongoS, mongoD on shards).

At Config server, go to config database and there shards -collection. Remove wanted line (shard).

Then you need to remove other traces to that removed shard, from chunks -collection (but way that your shard still works):

db.chunks.update({"shard":"<your removed shard -name>},{$set:{shard:"<name of you other shard>"}},{multi:true})

and from databases -collection:

db.databases.update({"primary":"<your removed shard -name>},{$set:{"primary":"<name of you other shard>"}},{multi:true})

After that, start everything (expect that removed shard) back online. First data bearing nodes (shards) and then last mongoS.

After that, you can start that "removed" shard as single replica set and mongodump data out and import it back to the cluster.. And you get back your "missing" data.


How to rollback last executed sql script fired as sysdba I have fired one sql script as sysdba which I have to fire using other oracle user.

I have already created some user and each application running on each oracle user. By mistake I had fired a script using one particular user but I have fired it using sysdba.

How to find .dbf file of sysdba user.
Can I revert my changes.
Does it affect other application.
I have list of tables and sequences. Can I drop individual table and sequence?
Do you have any better solution.

That script created lots of tables and sequences and inserted data into some tables.

Thanks!!!

The default tablespace of the SYS (sysdba) user is typically SYSTEM. From that you can figure out the .dbf file being used, although I'm not sure how this will help.
Maybe. Does your database run using flashback? Also, manually backing out changes (or write a script to undo what you did) might be possible.
Maybe.  What changed?
Yes.
Be more careful.  Ask this question on dba.stackexchange.com.

EDIT:

I would add that running scripts as the SYSDBA user should be VERY carefully evaluated as to necessity, and probably never used for application purposes.  As you can see, the SYSDBA user has essentially unlimited power over your database.


Avoid duplicates on self join Given the following table my_table :

my_column
------
A
B
C
D

I would like to be able to join on itself but without duplicate pairs like so:

-- SELECT a.*, b.* FROM my_table a JOIN my_table b ON a.my_column <> b.my_column;
-- something like so but without the duplicate pairs

a.my_column | b.my_column
------------+-----------
A           | B
A           | C
A           | D
B           | C
B           | D
C           | D

How should I tackle this in SQL ?

SELECT *
FROM my_table a
JOIN my_table b ON a.my_column < b.my_column


How can I add another .ibd file to my existing table when the memory space is full? I am working on an enterprise solution on MariaDB. The biggest challenge we are facing is to find a solution if our tablespace is full (.ibd file) and how can we add another .ibd file which will work like a joint memory space.

Considering we are using file_per_tablespace and MariaDB does not allow any DDM/DDL queries relating to tablespaces and datafiles.

You have the table is full error?

Check you are not out of disk space?

innodb_file_per_tablespace will affect tables newly created.

ALTER TABLE xxx FORCE will cause the table to be recreated in its new tablespace and the old one removed.


Are there any objective reasons to prefer a prefix over a suffix for an environment-specific login? When creating environment specific logins (development, qa, staging, production, etc.) I'm faced with the choice of making the denotation a prefix or a suffix to the login name.
Examples
prod_MyAppLogin
MyAppLogin_prod

Is there any reason why I should prefer one strategy over the other?
(Obviously, I should be consistent with whichever choice I make)

I'd rather have the environment be the suffix assuming I had to pick one convention.  Otherwise, it's too difficult to navigate through lists of login names that all use the same first five characters.

Of course, if given my choice, I would prefer to use neither the prefix nor the suffix.  I'd rather keep the same username in all environments and vary my password and TNS alias to identify which system I'm connecting to.


Purpose of Data Warehousing? I have been reading up on Data Warehousing and its purpose. The main purpose seems to be to track historical data and whatever changes may have occurred over time.

The DW book I am reading gives a number of reasons for using a DW schema...

1) Normalized schema may have 1000's of tables

2) Naming conventions may not be enforced

3) Data may be in multiple databases

4) Data quality issues

5) Historical data requirements

I am now working on a project where it has been suggested to me to use DW schema. However, none of the above 5 reasons apply to my situation.

1) Data is in region of 10 tables

2) Columns are named correctly
3) All data is in a single database

4) No reported Data Quality issues

5) No requirements for historical data

So, is tracking historical data the main reason to use DW?

As a BI consultant, my view on datawarehousing is that it provides (primarily) non-technical users with an easily accessible set of facts and dimensions.

Often, you'll see the following features in a data warehouse:

denormalized dimensions,
simplified fact tables and measures,
pre-aggregated balances,
pre-accumulated amounts over some time dimension,
often with some business logic (distributions, calculations, statistics) already applied to the data,
dimension data organized as a hierarchy where originally wasn't,
etc

All of this serves to simplify the understanding of, and accessibility to, the business data for people who aren't too comfortable writing SQL queries or using pivot table tools. Some of the greatest pitfalls in data warehousing and BI involve users not understanding the structure or meaning of the data, producing incorrect results and subsequently not trusting the BI solution.


In Google App Engine, what is the most effective many-to-many join model? The BigTable design rejects many of the philosophies of standard relational models, explicitly preferring denormalization to a big host of tiny tables.

One of the larger areas where this is a problem is in the modelling of many to many joins.

One way to model these joins is to violate first normal form, and put all interesting data in a db.ListProperty(). While this has the ability to be searchable from a query, I have not yet explored the performance implications of searching a list versus pulling another table.

As joins are not possible, it is possible to link tables through RelationshipProperties. Therefore, with enough effort, the standard intersection table (a table with a joint primary key which references both parent tables) can be created. Has anyone explored the performance hits of the various implementations?

-Edit-

While the List of Keys suggested in the documentation is indeed one way to do it, I'm interested in the performance and anomaly rates of that and other implementations. Is there utility in creating mutual lists of keys? Is the effort involved in the repeated gets worth the price? Is there a better way to do it?

I'm also working with the GAE datastore right now, you should check this article if you haven't already. If you have found something useful, please update your question.

EDIT:

I found this today, check it out.


How to do a select on a table based on a index-by table? I have a table called getSalas:

CREATE TABLE getSalas(
    ID_SALA NUMBER(4),
    NOME_CARATERISTICA VARCHAR2(20),
    VALOR_CARATERISTICA NUMBER(4)
);

The contents of that table are like this:

  1, 'Pool', 2

  1, 'A/C', 1

  2, 'A/C', 2
  ...

Which would mean that the room with the ID_SALA 1 has 2 pools and A/C. And the room with ID_SALA 2 has 2 A/C's.
I've also created the following datatypes:

CREATE OR REPLACE TYPE c_valor FORCE IS OBJECT (CARATERISTICA VARCHAR2(20), VALOR NUMBER(30));
/
CREATE OR REPLACE TYPE tabc_valor FORCE IS TABLE OF c_valor;
/

So my problem is, I need to know if a given set of characteristics exists in a room (e.g Has at least 2 pools and 3 unicorns etc...). For that I've created the following procedure:

CREATE OR REPLACE PROCEDURE getSalas_Carateristicas
(v_carateristicas IN tabc_valor, lista OUT SYS_REFCURSOR)
IS BEGIN
    OPEN lista FOR SELECT getSalas.ID_SALA
    FROM getSalas, TABLE(v_carateristicas) v_carateristicas
    WHERE getSalas.NOME_CARATERISTICA = v_carateristicas.CARATERISTICA
    AND getSalas.VALOR >= v_carateristicas.VALOR
    GROUP BY getSalas.ID_SALA
    ORDER BY getSalas.ID_SALA;
END;
/

v_carateristicas is where I receive the set of characteristics, I would look something like this:

  'A/C', 1

  'Extinguisher', 2
   ...

The problem is that the procedure is returning rooms that have match one or more of the set of characteristics, not all! For example, if I make v_carateristicas equal to:

  'Pool', 2

  'A/C', 2

And if getSalas looks like this:

  1, 'Pool', 2

  1, 'A/C', 1

  2, 'A/C', 2

It will return both rooms, although only room with the ID_SALA 1 matches what is asked.

This can be broken out into different steps if you want (i.e. variable for the count from the characteristics table).  But something with this logic should work.  Assuming unique constraint on (ID_SALA, NOME_CARATERISTICA) in getSalas, and unique data/constraint on (CARATERISTICA) in tabc_valor or whatever source data you have to compare to.  If those aren't necessarily unique, then you will only have one extra step to group by those columns first and sum the counts.

In one query:

with matches as (
    select gs.ID_SALA, count(1) ct
    from getSalas gs
    join tabc_valor tv on tv.CARATERISTICA = gs.NOME_CARATERISTICA
        and tv.VALOR <= gs.VALOR_CARATERISTICA
    group by gs.ID_SALA
), targetCount as (
    select count(1) ct
    from tabc_valor
)
select m.ID_SALA
from matches m
join targetCount tc on tc.ct = m.ct;


Are there any guidelines for setting innodb_stats_persistent_sample_pages in MySQL? The default value (20) seems to produce fairly accurate index cardinalities for small tables, which in turn results into good execution plans. But from what I've seen, accuracy starts falling off once your table grows.

It sounds reasonable, at least in my mind, that the number of pages to sample should be a percentage of the total number of rows.

I tried reading the manual and also tried searching for related literature, but so far I haven't found anything.

I could always go the trial and error route, but surely I'm not the only one with this conundrum.

Thanks in advance!

It's a crap shoot.

Too high --> takes too long; and not necessarily better stats
Too low --> inaccurate stats
Percentage --> 1% is close to hitting every block (for the typical table that has about 100 rows per block).
As table grows, InnoDB allegedly re-ANALYZEs after 10% growth, so no manual action needed.
Most tables have decent stats from 20.
Tables with uneven distribution won't benefit from changing the '20', at least not until some decent "histogram" stats are implemented.  Then, Optimization becomes much more complex, and potentially slower.

Bottom line:  Leave it at 20.  Keep the slowlog on.  Tackle the worst entry in the slowlog, with an eye for solutions that don't depend on the '20'.


Increased memory to server, but do I need to change page-file size? I'm running SQL Server 2017 on Windows Server 2012 R2 and have increased the memory from 256 GB to 512 GB.
I've noticed the following configuration for Virtual Memory:

C: [OS]   = 2 GB
G: [DATA] = None
H: [LOGS] = None
T: [TEMP] = None

There is a lot of advice out there talking about setting the size to 1.5 or 2 times the size of the memory that is installed.  Am I reading this correct or have I misread the articles and they apply to something else?

There is also articles suggesting not to worry about the virtual memory as SQL should only be using RAM and not virtual memory, and the OS only requires 2GB of virtual memory?

I believe the 2x size of RAM recommendation is outdated and unrealistic.  If a server only has 32 GB of RAM it might make sense,  but it's a waste of expensive disk space to make it 2x on a system that had a massive amount of memory.
There are two primary purposes of the pagefile:

To use as a swap file for memory.  If SQL Server and other applications are configured correctly to limit the amount of memory available, it will never be needed for this purpose.  If the system does have to use the pagefile to swap memory, system performance will be so bad you'll effectively have an outage and be forced to deal with it immediately.  So if you configure a 1 TB pagefile, it won't help performance when the system starts swapping to disk and you'll be forced to do something about it before it needs to swap even 100 GB.

To hold a memory dump if the system crashes (on the volume where OS is installed).  Again, if a  server only has 32 GB of RAM, it makes sense to configure it to do a full memory dump if the system crashes.  However, it is rarely necessary for diagnostics purposes to need a full memory dump, anda kernel memory dump is the reasonable choice on a system that has a lot of memory.  Also, Windows crashes are much less frequent these days than in 1996 (or whenever) when that 2x recommendation was made.  Also, if the system crashes and you get a 1 TB memory dump (you'll need another TB free on c: for it), good luck zipping that sucker and transferring it to Microsoft.  I would only configure for a kernel dump and only change that if the system is crashing and Microsoft needs a full memory dump to diagnose the issue.

So if you're super paranoid (which is certainly justified in some situations) and have 2 TB to allocate that will likely never be used, then a gigantic pagefile is a consideration.  However, it's rarely going to be really necessary.
So my answer is that you are highly unlikely to need to increase the pagefile size.  It probably hasn't been used at the current size, and with more memory now, it's even less likely to be used, so it would follow that there's no need to waste more disk space on a bigger one.


How can I find databases with a large number of virtual log files? I have several databases on a production server that are in the hundreds of gigabytes, and have many thousands of transactions running through them on a daily basis.

Almost all these databases are mirrored using SQL Server Mirroring.

Even though we have carefully planned the physical log file sizes to match expected log file activity; occasionally something goes wrong and logs need to grow beyond our predicted maximum.  We have setup all log files to grow by 8192MB, however when the database is under pressure to grow the log file it sometimes will only grow the log in very small chunks, thereby creating in some cases hundreds of thousands of virtual log files (VLFs).

I came to understand the importance of keeping the number of VLFs low when one of our production databases unexpectedly went into recovery with over 200,000 VLFs.  Recovery took 20+ hours; during which time a portion of our business could not operate.

I need a solution that can monitor the number of virtual log files for all databases present on a server, sending an alert email if any particular log file has more than a given number of VLFs.

I know DBCC LOGINFO; returns the list of VLFs, however, I don't want to manually run this.

I created the following SQL statement that creates a nice table listing the databases, along with the number of VLFs however, I do not know how I can put this into a SQL Agent job to email our team whenever any database has over "x" number of VLFs.

DECLARE @cmd_per_database_prefix nvarchar(max);
DECLARE @cmd_per_database nvarchar(max);
DECLARE @database_name nvarchar(255);
SET @cmd_per_database = '';
SET @cmd_per_database_prefix =
'
    SET NOCOUNT ON;
    DECLARE @vlf_count_table TABLE (database_name nvarchar(255), vlf_count int);
    DECLARE @params nvarchar(max);
    DECLARE @db_name nvarchar(255);
    DECLARE @vlf_count int;
    SET @params = ''@db_name nvarchar(255) OUTPUT, @vlf_count int OUTPUT'';
    DECLARE @cmdGetVLFCount nvarchar(max);
    SET @cmdGetVLFCount =
    ''
        DECLARE @tab TABLE
        (
            FileId int
            , FileSize nvarchar(255)
            , StartOffset nvarchar(255)
            , FSeqNo nvarchar(255)
            , Status int
            , Parity int
            , CreateLSN nvarchar(255)
        );
        DECLARE @cmd nvarchar(max);
        SET @cmd = ''''DBCC LOGINFO;'''';
        INSERT INTO @tab
        EXEC sp_executesql @cmd;
        SET @db_name = db_name();
        SET @vlf_count = (select count(*) FROM @tab t);
    '';
';
DECLARE cur CURSOR FOR
SELECT NAME
FROM sys.databases
WHERE database_id > 4 and state=0;
OPEN cur;
FETCH NEXT FROM cur INTO @database_name;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd_per_database = @cmd_per_database +
    '
        EXEC ' + @database_name + '.sys.sp_executesql @cmdGetVLFCount, @params, @db_name OUTPUT, @vlf_count OUTPUT;
        INSERT INTO @vlf_count_table (database_name, vlf_count) VALUES (@db_name, @vlf_count);
    ';
    FETCH NEXT FROM cur INTO @database_name;
END
SET @cmd_per_database = @cmd_per_database_prefix + @cmd_per_database + char(13) + char(10) + 'select * from @vlf_count_table t;';
EXEC sp_executesql @cmd_per_database;
CLOSE cur;
DEALLOCATE cur;

Attempting to output this using INSERT...EXEC into a table for attaching to an email using sp_send_dbmail is proving futile.

SQL Server chokes with:

An INSERT EXEC statement cannot be nested.
Msg 8164, Level 16, State 1, Line 5

Here is a slightly simpler approach that avoids the cursor and nested exec:

SET NOCOUNT ON;

CREATE TABLE #to
(
  DBName SYSNAME,
  FileCount INT
);

DECLARE @v INT;
SELECT @v = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32),
  SERVERPROPERTY('ProductVersion')), 4));

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'CREATE TABLE #ti
  (
    ' + CASE WHEN @v >= 11 THEN 'RecoveryUnitId INT,' ELSE '' END + '
    FileId int
    , FileSize nvarchar(255)
    , StartOffset nvarchar(255)
    , FSeqNo nvarchar(255)
    , Status int
    , Parity int
    , CreateLSN nvarchar(255)
);';

SELECT @sql = @sql + N'
  INSERT #ti EXEC ' + QUOTENAME(name)
    + '.sys.sp_executesql N''DBCC LOGINFO WITH NO_INFOMSGS'';
  INSERT #to(DBName,FileCount) SELECT N''' + name + ''', COUNT(*) FROM #ti;
  TRUNCATE TABLE #ti;'
FROM sys.databases
WHERE database_id > 4 AND [state] = 0;

EXEC sp_executesql @sql;

SELECT DBName, FileCount FROM #to -- WHERE FileCount > [some threshold];

DROP TABLE #to;


Can I update only the needed changes in a database? First of all, I apologize if you can't understand very well what I am saying and it can be edit for better understanding of my post. The next example is for better understanding of databases with control version.

Suppose I have a software used by several businesses. This software need a database to function correctly, that means every business have one. I have a central database using migrations and scripts to have control version but this central database do not comunicate with the others. Every business have a different version from each other, including the central database that always have the last version.

To update the databases to my last version I apply idempotent scripts (like Entity Framework) so I can detect the version and continue from there. Let say the business with the version 15.11 have an error with the database and need to be solve right away. I recreate the bug, fix it, create a script, and bring the update to the company. Now this company have a different version, like 15.11.1. and the last script it's included in the central database.

So here comes my question, if I decide to update in the future that same database to the last version, my idempotent script do not apply this change again?

Another question is: Can I apply differents scripts in a database? i.e. if in the update are 5 scripts and I only want to apply the #1, #3 and #5. The idea is for reduce the updating time if I have change already applied.

I am open for all kind of opinions and if I have some concepts wrong, please feel free to correct me.

Thank you.

I would suggest reading this whitepaper on database version control from DBmaestro TeamWork (where I work)

Basically, it discuses how database enforced change management combines the enforcement of version control processes on the database objects with the generation of the deployment script when required based on the version control repository and the structure of the environment at that time.

This approach uses build and deploy on-demand, which means the deploy script is built (generated) when needed, not as part of development. This allows for efficient handling of conflicts, merges, and out-of-process changes.

It handles the challenges by:

Ensuring all database code is covered – structure, business logic written in the database language, reference content, database permissions, and more are managed

Ensuring the version control repository can act as the single source of truth – the enforced change policy prevents anyone (developers, DBAs) using any IDE (even command line) from modifying database objects which were not checked-out before and checked-in after the change. This guarantees that the version control repository will always be in sync with the definition of the object at check-in time.

Ensuring the deployment script being executed is aware of the environment status when the script is executing – building (generating) the deployment script when needed (just before executing) guarantees it is aware of the current environment status.

Ensuring the deployment script handles conflicts and merges them – by using baselines in the analysis, the nature of the change is known and the correct decision whether to promote the change, protect the target (ignore the change), or merge a conflict is easy.

Generating deployment scripts for only relevant changes – the integration with application life-cycle management (ALM) and change management systems (CMS) enables you to assign a reason to the change, as is done in the file-based version control or task management system.

Ensuring the deployment script is aware of the database dependencies – the sophisticated analysis and script generation algorithm ensures the DDLs, DCLs, and DMLs will be executed in the correct order based on the database dependencies, including inter-schema dependencies.


Create index clustered X NonClustered with include I have a table like :

create table x (a varchar(20),
b varchar(30),
c varchar(30),
primary key clustered(a asc))

so I'm wondering if I create an index like:

Create nonclustered index test on x(a) include (b,c)

that index will make my querys that use columns a,b,c  faster?

In this case, the non-clustered index is essentially a duplicate of the primary key*. It will take up extra space and make inserts/updates/deletes slower, but it will not add any benefit.

The reason is that any query that can be answered by seeking on column a can perform that seek on the primary key and has no need for the non-clustered index.

However, if your actual table has many additional columns beyond a, b, and c (e.g., let's say there are 100 other columns as well), it's possible that the non-clustered index could improve queries that use only a, b, and c. In that case, it would provide a smaller data structure that can be used to access only these three columns. It would depend on your workload whether this is worth the cost of maintaining the index.

* The primary key is the clustered index here and, as Max Vernon points out, the clustered index is the table. So another way of putting it is that your non-clustered index is duplicating the whole table.


Rows as columns in MySQL My table, t, has the following format:

I would like to transform this table into:

The COUNT_XXXXX columns are determined by counting the number of rows where CLICKSKEY is not NULL organised by SUBJECT_TYPE.  The COUNT_CLICKED column counts the number of rows where CLICKSKEY is not NULL independent of SUBJECT_TYPE.

Could anybody help?

You can use the CASE statement and return a value every time the condition is true. In the end you just have to sum those values.

SELECT
  DEALER_ID,
  SUM(CASE SUBJECT_TYPE WHEN 'Marketing' THEN 1 ELSE 0 END) AS COUNT_MARKETING,
  SUM(CASE SUBJECT_TYPE WHEN 'Random' THEN 1 ELSE 0 END) AS COUNT_RANDOM,
  SUM(CASE SUBJECT_TYPE WHEN 'Alert' THEN 1 ELSE 0 END) AS COUNT_ALERT,
  COUNT(*) AS COUNT_CLICKED
FROM t
WHERE CLICKSKEY IS NOT NULL
GROUP BY DEALER_ID;


session sql mode + default sql mode I am testing my application in mysql 5.7 and have some code that doesn't work with ONLY_FULL_GROUP_BY ON. I will fix this in a later release. I found a way to turn this off for just my application by executing 'SET SESSION sql_mode=""' before running any queries.

I have 2 questions:

Will this work on shared host services that don't have root access to database? It appears to have worked on my testing.
Since the mode is set to '' what does this actually mean? To me it seems like it is setting it back to nothing and making at non restrictive. (My other thought is that it set it to nothing and lets mysql decide what to do).

Based on my reading of the manual empty mode use to be default in mysql 5.1 but has become more restrictive with each version. For mysql 5.7 it is ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

So based on this it would seem that mode is a list of rules and if it is empty non the rules apply?

Also if I set the mode to "" will this always disable the modes set by server?

SET SESSION variables apply only to the current connection.
SET GLOBAL variables apply as the default when a connection is made.
SESSION does not need root access; GLOBAL does.
Be careful what you set it to:

Let's say you find that the default is

mysql> SELECT @@sql_mode;
+--------------------------------+
| @@sql_mode                     |
+--------------------------------+
| STRICT_ALL_TABLES,NO_ZERO_DATE |
+--------------------------------+

Blindly setting it will turn of those two while setting the one you want:

mysql> set session sql_mode = 'ONLY_FULL_GROUP_BY';
mysql> SELECT @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+

So, build the full string:

mysql> SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY';

mysql> SELECT @@sql_mode;
+---------------------------------------------------+
| @@sql_mode                                        |
+---------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_DATE |
+---------------------------------------------------+

Addenda

If you want to add/subtract a mode without knowing what mode(s) are already set, this seems to work:

Turn on (for session only):

SET @@sql_mode = CONCAT(@@sql_mode, ',', 'ONLY_FULL_GROUP_BY');

Turn off (for session only):

SET @@sql_mode =
        REPLACE(
        REPLACE(
        REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY,', ''),
                           ',ONLY_FULL_GROUP_BY', ''),
                            'ONLY_FULL_GROUP_BY', '');

Note carefully the commas.


what does "\" symbol mean? I am new to database learning and came across this \ symbol. I googled but couldn't find what it meant.

It was used in the context Q = R\S, where Q, R, and S are relations.

(The = sign here means "is equivalent to").

The \ symbol is often used in mathematics for set difference, besides dash or minus symbol (-)

Since tables and relations are very similar to sets and the operations of union, intersection and difference are similarly defined (see Wikipedia: Relational algebra, set operators), the \ symbol is also sometimes used for difference.

If the Q = R\S expression is found in a database theory book and Q, R and S are relations, it means that Q is a relation (table) that has the same attributes (columns) as R and S and also has those tuples (rows) that are in relation R but not in relation S.


Why Does UNPIVOT Work on Compatibility Level 80 DB? I'm trying to pull data using UNPIVOT on a SQL Server 2008 SP3 database running at Compatibility Level 80. This should mean UNPIVOT doesn't work, but in my case it does under certain circumstances...

Works:

Independent SELECT query of the form:

SELECT...FROM...UNPIVOT...WHERE...GROUP BY

Doesn't work:

Same query, within a LEFT JOIN () on other tables on different databases within the same server. All at compatibility level 80.

I get the usual error message:

Msg 325, Level 15, State 1, Line 165
Incorrect syntax near 'UNPIVOT'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help for
the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

UNPIVOT workarounds seem cumbersome and I'd like this query to be self-contained and refreshable if possible. It seems logical that if I can get the query to work in isolation, it should be possible to use it in a JOIN.

Questions:

Why does this work sometimes?

How can I achieve the UNPIVOT sub-query inside the JOIN under these conditions?

Unpivot Sub-Query: (By request...)

SELECT
    YEAR(CAST('1-'+UnPiv.[Date] AS DATE)) "Year",
    MONTH(CAST('1-'+UnPiv.[Date] AS DATE)) "Month",
    CASE [Channel_Org]
        WHEN 'TPR' THEN 'ERP'
        ELSE [Channel_Org]
    END AS [Channel_Org],
    ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
        [OPEX_Group] = 'Postpaid SAC' THEN [Amount] END),0) "Post EIP",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
        [OPEX_Group] = 'Prepaid SAC' THEN [Amount] END),0) "Pre EIP",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'EIP' AND
        [OPEX_Group] = 'SRC' THEN [Amount] END),0) "Upg EIP",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
        [OPEX_Group] = 'Postpaid SAC' THEN [Amount] END),0) "Post MDF",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
        [OPEX_Group] = 'Prepaid SAC' THEN [Amount] END),0) "Pre MDF",
    ISNULL(SUM(CASE WHEN [GL Desc] = 'MDF (OEM)' AND
        [OPEX_Group] = 'SRC' THEN [Amount] END),0) "Upg MDF"
FROM [Channel_Steering].[dbo].[AQ1_OPEX_View]
UNPIVOT (Amount FOR [Date] IN ( [Jan-14],
                                [Feb-14],
                                [Mar-14],
                                [Apr-14],
                                [May-14],
                                [Jun-14],
                                [Jul-14],
                                [Aug-14],
                                [Sep-14],
                                [Oct-14],
                                [Nov-14],
                                [Dec-14])) UnPiv
WHERE   (   [Channel_Org] IN ('Retail','TPR')
                AND
            [GL Desc] IN ('MDF (OEM)', 'EIP')
        )
GROUP BY
    [Channel_Org],
    YEAR(CAST('1-'+UnPiv.[Date] AS DATE)),
    MONTH(CAST('1-'+UnPiv.[Date] AS DATE))

The answer is in the error message:

  You may need to set the compatibility level of the current database to
  a higher value to enable this feature.

Where 'current database' means the context database - i.e. the database the query is executed in, which is not necessarily the same as the database referenced in the query. For example, the following produces the error message:

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks
SET COMPATIBILITY_LEVEL = 80;
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV
UNPIVOT
(
    Value
    FOR ColumnName IN
        (
        VersionDate,
        ModifiedDate
        )
) AS U;

The following executes successfully (assuming tempdb has a compatibility level > 80):

USE AdventureWorks;
GO
ALTER DATABASE AdventureWorks
SET COMPATIBILITY_LEVEL = 80;
GO
USE tempdb; -- Context database
GO
SELECT
    U.SystemInformationID,
    U.[Database Version],
    U.ColumnName,
    U.Value
FROM AdventureWorks.dbo.AWBuildVersion AS ABV -- Still references data in AW DB
UNPIVOT
(
    Value
    FOR ColumnName IN
        (
        VersionDate,
        ModifiedDate
        )
) AS U;

You need to check what the context database is for the connection that produces the error.


Is there data visualisation tool for postgresql which is capable of displaying inter schema relations as well? Operating system used is linux. I have tried Navicat and SQL Power Architect. They did display relations between tables in the same schema. I have some foreign key constraints which reference tables in a different schema.

Am I missing something with respect to Navicat and PostgreSQL Maestro? Can they not display the inter schema relations?
Is there data visualisation tool for postgresql which is capable of displaying inter schema relations as well?

I don't think there is one for PG (though I think that pgAdmin will have something like this in the future).

If I need visualization of PostgreSQL data schema, I use the MySQL Workbench (I know, I wil burn in hell for this...). This works pretty good, though i throws some errors with SQL not known to MySQL (though this does not stop the process on the first error, it continues). This worked pretty well for me in the past.

Be warned though: many of the features only work with MySQL somewhere in the back or SQL generated by/for MySQL. This is only usefull if you want plain visualization.


RMAN backup is stuck/taking a long time I am trying to run an RMAN backup that is either hung or is taking a very long time. Some background:

I inherited this database, and I am unfamiliar with Oracle, as I am an SQL Server guy, so please point out / forgive any basic gaps in my knowledge
This database is large but infrequently-used, and is loaded up with external data for analysis.  There are no transactions.  Therefore, the database is in NOARCHIVELOG mode.
I need to do a one-time backup for now, so I think I don't need a recovery catalog.
I'm not quite sure of the database size, but it is on the order of 1TB

Here's what I did, cobbled together from various sources on the internet:

> rman target mydatabase

shutdown;
startup mount;
run {
    allocate channel ch1 device type disk format 'C:/Backup_Folder/%U';
    backup format 'C:/Backup_Folder/db_%t_%s.bk' (database);
    release channel ch1;
}

The backup seems to be stuck on channel ch1: starting piece 1 at 01-MAY-15

RMAN creates a tiny (90K) file in C:/Backup_Folder but then nothing else has happened for over an hour.  Do I just wait?  Am I doing something wrong?

Don't specify two conflicting FORMATs (you have one in the ALLOCATE CHANNEL command and one in the BACKUP command).

If and when you need different FORMATs (e.g. one for database datafile backups, one for archivelog backups and one for controlfile backups) specify them explicitly with the BACKUP command.

Eg:

> rman target mydatabase

    shutdown;
    startup mount;
RUN {
      ALLOCATE CHANNEL ch1 TYPE
        DISK FORMAT 'C:\Backup_Folder\mydatabase%d_DB_%u_%s_%p';
      BACKUP DATABASE;
      RELEASE CHANNEL ch1;
    }
    EXIT;

Note: I noticed that you provided the path starting with "C:/". Shouldn't it start with "C:\"?


Get top most parent by nth child id? Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the parent_id column this is a good and normalized technique i agree but there is a disadvantage also, it’s slow and inefficient. This is mainly caused by the recursion like for each parent we have to run the query again and again to make a tree

SELECT id FROM `table` WHERE parent_id=something

I have looked at the solutions some might try to do it with any programming language by running query again and again which makes a loads on server , Some have provided the stored procedure but that also involves the recursion.

So my question is can we do it with one database query for the tree(joins or subqueries) if we know the depth or if we don't know if it is possible so how can we get the top most parent(i.e parent_id=0) of any child if it is not possible then why this technique is so famous while it has the flaws or we have another solution for this? . i have added the sql fiddle but it only has the schema

FIDDLE

What you are looking is simply not facilitated by MySQL as of writing this answer. Common Table Expressions (CTEs) are supported by several databases. @a_horse_with_no_name posted an answer naming those RDBMSs.

I have posted recursive Stored Procedures: Find highest level of a hierarchical field: with vs without CTEs. However, you emphasized not using any Stored Procedures. To do what you are asking does require Stored Procedures in MySQL.

Compromise Solution...

You will still need a Stored Procedure, but not using recursion. Using a simple loop, you can construct a self-join query and executed it dynamically. Given the depth of the tree you are looking for, here is such a Stored Procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `dianuj`.`GetTopParentGivenDepth` $$
CREATE PROCEDURE `dianuj`.`GetTopParentGivenDepth` (GivenDepth INT)
BEGIN

    DECLARE x1,x2 INT;

    SET x = 0;
    SET y = 1;
    SET @SQ = 'SELECT DISTINCT A0.id FROM prarent A0';
    WHILE y < GivenDepth DO
        SET @SQ = CONCAT(@SQ,' INNER JOIN prarent A',y,' ON A',x,'.id = A',y,'.parent_id');
        SET x = y;
        SET y = x + 1;
    END WHILE;
    SET @SQ = CONCAT(@SQ,' WHERE A0.parent_id = 0');
    SELECT @SQ;
    PREPARE stmt FROM @SQ;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END $$

DELIMITER ;

After loading your data into MySQL on my desktop, I ran the Stored Procedure with...

Depth 1

mysql> call GetTopParentGivenDepth(1);
+--------------------------------------------------------------+
| @SQLSTMT                                                     |
+--------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 WHERE A0.parent_id = 0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Depth 2

mysql> call GetTopParentGivenDepth(2);
+------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                |
+------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id WHERE A0.parent_id = 0 |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
|  2 |
+----+
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Depth 3 .. 6

mysql> call GetTopParentGivenDepth(3);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id WHERE A0.parent_id = 0 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call GetTopParentGivenDepth(4);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id WHERE A0.parent_id = 0 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call GetTopParentGivenDepth(5);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id INNER JOIN prarent A4 ON A3.id = A4.parent_id WHERE A0.parent_id = 0 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call GetTopParentGivenDepth(6);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id INNER JOIN prarent A4 ON A3.id = A4.parent_id INNER JOIN prarent A5 ON A4.id = A5.parent_id WHERE A0.parent_id = 0 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Notice that Depth 6 has no return value. To find the maximum depth, you would have to iterate on it until no rows comes back.

CAVEAT

The worst case scenario would be to use the number of rows in the table as the depth. Such a tree would be nothing more than a degenerate linked list.
I cannot give you any guarantees on query performance, such on SQL construction and subsequent execution. Any kind of recursion would be the sole responsibility of the Query Parser.

Give it a Try !!!


pgAdmin 4, no built-in SSH tunnel config? Just tried using pgAdmin 4 for the first time.
It looks like there's no built-in SSH tunnel config for connection.
I searched the docs but there's no mention of the feature.

Is it gone for good?

UPDATE:

SSH Tunneling is now available as of version 3.1 - https://www.pgadmin.org/docs/pgadmin4/development/release_notes_3_1.html

Original answer:

Looks like they plan on having it for v2.0, which could be up to a year away, though they say that they are open to having it sooner.

  I hope we'll be able to add SSH support sooner rather
  than later.

https://www.mail-archive.com/pgadmin-support@postgresql.org/msg15175.html


Windows 2000 can't connect to SQL Server 2014 We have an old workstation running Windows 2000 that must connect to MSSQL14 running on Windows server 2012 R2.
When i try to create an ODBC using:

sa
TCP/IP (ex. 123.456.789.012)
not dynamic port 1434 (the one we use on every other workstation successful)

it gives me this error:

  Impossible to connect

  SQLState: '01000'

  Sql server error: 1006 Impossible to connect

  SQLState: '08001'

  Sql Server error: 17

I think it's driver fault, but I can't find something useful...
On the workstation there is this driver: Sql Server driver 2000.80.194.00

We need to change this workstation, but I need to make it works ASAP until that moment.

Can someone help me?

Community wiki answer:

I would try installing the SQL Server 2005 Native Client in the Feature Pack for Microsoft SQL Server 2005 - April 2006.

Please note also that post says Windows 2000 Service Pack 4 is required to be supported.


Open access relational database I have some very basic knowledge in SQL which I mainly learn from some theoretical data science course. I would like to write a few lines of SQL just to experiment and train myself.

Can you please suggest a few free relational database for which I could write a few queries? Those databases can be about health, economy, climate change, social interaction, genetics, .... whatever seems to be of interest to most people.

https://www.lynda.com/ has free online classes. Some of which include practice databases and datasets to work with. Or you might try Gale. https://education.gale.com/l-slcl/